How to conditionally extract a value from a nested array object in AEP Data Prep (ingestion)? | Community
Skip to main content
A
Ankith Menon
Level 1
April 27, 2026
Question

How to conditionally extract a value from a nested array object in AEP Data Prep (ingestion)?

  • April 27, 2026
  • 1 reply
  • 11 views

I have a source JSON with an array of objects. Each object has nested fields including an identifier and a code. I need to extract the identifier from the array item where a specific nested field matches a given value.

Example Structure:

{
  "items": [
    {
      "details": {
        "id": null,
        "type": { "code": "ABC" }
      }
    },
    {
      "details": {
        "id": "123456",
        "type": { "code": "XYZ" }
      }
    }
  ]
}

Goal: Extract details.id from the array item where details.type.code == "XYZ".
 

The array length is not fixed — it could have 1 to N items, and the matching item could be at any index.

 

Current approach (works but not scalable):
coalesce(
  iif(items[0].details.type.code == "XYZ", items[0].details.id, null),
  iif(items[1].details.type.code == "XYZ", items[1].details.id, null),
  iif(items[2].details.type.code == "XYZ", items[2].details.id, null),
  null
)

Question:
Is there a cleaner built-in Data Prep function to conditionally filter an array by a nested field value during ingestion? Or is the coalesce/iif per-index approach the only available option?

1 reply

giuseppebaglio
giuseppebaglio
Level 10
April 27, 2026

hi ​@Ankith Menon,

Rather than the coalesce/iif per-index approach, consider using array_to_map, which converts an object array into a map keyed by a field value . This can eliminate index-based hardcoding:

// Step 1: Convert array to a map keyed by the type code
array_to_map(items, "details.type.code")

// Step 2: Access the target entry by key and drill down
array_to_map(items, "details.type.code")["XYZ"].details.id

The resulting map would look like { "ABC": {...}, "XYZ": {...} }, so you can directly retrieve the object for "XYZ" without iterating by index. Note that the KEY parameter supports field names within the array objects — test whether a nested path like "details.type.code" is accepted in your environment, as the docs describe it as "a field name in the object array" .

Terms & ConditionsAccessibility statement

Loading footer...