nested arrays using sql query insertion to a dataset not working

Question

I am trying to insert nested arrays output from a sql query into another dataset but it is not working.

insert into b_array_processed_data_testing(_spnam.profileIds.B_UID,_spnam.arrayOfObjects)
SELECT
b_array.UID,
b_array.arrayOfObjects
FROM
(WITH grouped_data AS (
SELECT
_spnam.profileIds.B_UID as UID,
ARRAY_AGG(STRUCT(_spnam.arrayOfObjects.coolestInt,_spnam.arrayOfObjects.FavDessert)) AS arrayOfObjects
FROM
b_array_staging_poc_testing
GROUP BY
_spnam.profileIds.B_UID
)
SELECT
UID,
arrayOfObjects
FROM
grouped_data;)b_array

nnakirikanti · Accepted Answer

@ritwikmoWhen 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.

https://experienceleague.adobe.com/en/docs/experience-platform/query/key-concepts/nested-data-structures#use-insert-into-to-update-nested-data-fieldshttps://experienceleague.adobe.com/en/docs/experience-platform/query/key-concepts/nested-data-structures

~cheers,

Naresh Nakirikanti.

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded