Looping Arrays in AA-AEP Data connector

Hey ​@KishanSa2 

There is no data prep function which supports element-wise conversion in AEP. However, for destinations you can use “transformArray” to transform individual values to a dateTime field. Refer more in this documentation. 

Therefore, considering your case, you can’t do that in the mapping step and requires processing after ingestion. Hope this helps!

Best Regards,

Ganesh Kumar C

Hi KishanSa2, try asking this same query to Rovr: My ExL based chatbot that provides answers to queries from different Adobe Experience Cloud solutions.

https://thelearningproject.in/tools/rovr/

Here’s the answer to your query

 

# Mapping Array of Date Strings to XDM dateTime Array in AEP Data Prep

This is a nuanced implementation question. Let me address what Data Prep supports based on documented capabilities, specifically in the context of the **Analytics Source Connector** workflow.

---

## What the Analytics Source Connector Video Demonstrates

The Analytics Source Connector workflow includes **Data Prep** capabilities that allow you to:
- **Map Analytics variables to custom XDM fields**
- **Perform transformations and calculations** inline during the mapping step
- Apply **data manipulation functions** before data lands in AEP datasets

---

## Data Prep Function Support for Date Conversion

Data Prep provides documented functions for date/time transformation. The key function relevant to your use case is:

### `toDateTime(value, dateFormat)`
Converts a string value to a `dateTime` type using a specified format pattern.

**Example for a scalar field:**
```
toDateTime(sourceField, "yyyy-MM-dd")
```

---

## Array Element-Wise Transformation: What Data Prep Supports

This is where your specific challenge lies. Data Prep does support **array-level operations**, but with important constraints:

### Supported: `transform(array, function)` Pattern
Data Prep's documented functions include the ability to apply a function element-wise across an array using the **`transform`** function:

```
transform(arrayOfDateStrings, toDateTime(%, "yyyy-MM-dd"))
```

Where `%` acts as the **placeholder** representing each element in the array during iteration.

**Example mapping:**
| Source Field | Transformation | Target XDM Field |
|---|---|---|
| `_experience.analytics.customDimensions.listProps.prop1` (array) | `transform(sourceArray, toDateTime(%, "yyyy-MM-dd'T'HH:mm:ss'Z'"))` | `xdm.mySchema.dateTimeArray[]` |

---

## Important Constraints and Caveats

### 1. Analytics Source Connector Mapping Limitations
- The Analytics connector **auto-maps many fields** but custom/list variable arrays may require **manual mapping rules** in the mapping step of the source connection workflow.
- Not all Analytics variables expose cleanly as typed arrays — **list variables** (e.g., `listvariables`) come through as delimited strings, which means you may need to **first split, then transform**:

```
transform(split(listVarField, ","), toDateTime(trim(%), "yyyy-MM-dd"))
```

### 2. Format String Must Match Exactly
- If your date strings are inconsistently formatted across array elements, **Data Prep will null-out** elements it cannot parse rather than failing the entire record. Validate source format consistency beforehand.

### 3. XDM Target Field Must Be Declared as an Array of `dateTime`
- Your XDM schema must have the target field defined as **`type: array`** with `items.type: string` and `items.format: date-time` (XDM dateTime).
- Data Prep respects the target schema type during mapping — mismatches result in ingestion errors at the dataset level.

### 4. Nested Arrays
- Data Prep does **not** support transformations on **arrays of arrays** (multi-dimensional arrays). If your Analytics data produces nested array structures, post-ingestion processing would be required.

---

## Decision Framework: Data Prep vs. Post-Ingestion

| Scenario | Recommendation |
|---|---|
| Flat array of consistently formatted date strings | ✅ Use `transform()` in Data Prep |
| List variable as delimited string needing split + convert | ✅ Use `split()` + `transform()` in Data Prep |
| Inconsistently formatted dates across elements | ⚠️ Pre-validate or use post-ingestion (AEP Query Service / Spark) |
| Nested/multi-dimensional arrays | ❌ Post-ingestion required (AEP Query Service) |
| Volume too high for row-level function overhead | ⚠️ Benchmark first; consider Query Service batch transforms |

---

## Post-Ingestion Alternative: AEP Query Service

If Data Prep `transform()` does not satisfy your requirements, **AEP Query Service** (built on PostgreSQL/Spark SQL) supports:

```sql
SELECT
  ARRAY_AGG(TO_TIMESTAMP(date_str, 'yyyy-MM-dd')) AS dateTimeArray
FROM (
  SELECT EXPLODE(sourceArrayField) AS date_str
  FROM source_dataset
)
```

This approach writes results to a **derived dataset** mapped to your target XDM schema.

---

## Summary

- **Yes**, Data Prep supports element-wise date conversion on arrays via the `transform(array, toDateTime(%, "format"))` pattern — this is the recommended first approach.
- The Analytics Source Connector mapping UI is where you apply these rules — no upstream changes are needed.
- Post-ingestion via **Query Service** is the fallback for complex array structures, inconsistent formatting, or nested arrays.