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

Question

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?

giuseppebaglio · Accepted Answer

Since you're hitting a hard ceiling in Data Prep, consider these alternative solutions:

Normalize upstream — Flatten or pre-select the matching item before the payload reaches AEP. If you control the source system or an ETL layer (e.g., via Azure Data Factory, AWS Glue, or a custom webhook), emitting details_XYZ_id as a flat field is the most robust fix.
AEP Query Service (post-ingestion) — If the raw array is landing in a dataset, you can use FILTER or TRANSFORM higher-order SQL functions in Query Service to extract the value and write it to a derived dataset. This decouples the transformation from the ingestion mapping.

giuseppebaglio · Answer

hi ​@Ankith Menon,Rather than thecoalesce/iifper-index approach, consider usingarray_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 codearray_to_map(items, "details.type.code")// Step 2: Access the target entry by key and drill downarray_to_map(items, "details.type.code")["XYZ"].details.idThe resulting map would look like{ "ABC": {...}, "XYZ": {...} }, so you can directly retrieve the object for"XYZ"without iterating by index. Note that theKEYparameter 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" .

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded