nested arrays using sql query insertion to a dataset not working

[RitwikMo]

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 solution]

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

~cheers,

Naresh Nakirikanti.