AEP sql insert into array element

[supratim320]

Hi Everyone,

We have created a schema which contains an array type node and in this array there are columns such as SKU, price, productname, quantity etc. We will have to ingest data into this dataset via query service. So we have created multiple CTEs to get what we needed:

This is how we form the node in question via CTE (table name: abandon_cart_data): collect_list(STRUCT(`productName`, `SKU`,`quantity`, `price`)) as cartContents

INSERT INTO abandon_cart_sales_dataset

SELECT
STRUCT (email AS address) personalEmail,
STRUCT (
AccountNumber AS AccountNumber, leadStatus, LeadID AS LeadID ,
array(cartContents) AS cartContents
, cartValue AS totalCartValue, currencyCode AS currencyCode
, latestAddToCart AS latestAddToCartDate, website, campaignCode
, leadSource, number_of_line_items_in_cart AS numberOfLineItemsInCart
) _abc
FROM abandon_cart_data

It is throwing error as: 

Batch creation failed

[brekrut · Accepted solution]

Hello @supratim320 

When you are working with complex data structure (array's) you will need to use the collect_set function to write data into the array of the target schema dataset.

Please use the following as reference.

https://experienceleague.adobe.com/en/docs/experience-platform/query/key-concepts/nested-data-struct...