Need to populate the object array fields

[BellaSnow5378]

Hi, @arpan-garg @Kumar_Saurabh_ @abhinavbalooni @ssj2 @_Manoj_Kumar_ @jantzen_b 

We have created a schema in which we have an object array inside that attributes and we want  fetch those  records from the dataset and insert into new dataset remember we have to use struct can u just give a sample query or guide me how to perform this  

[Kumar_Saurabh_ · Accepted solution]

Hi @BellaSnow5378,

Query:

Insert into dataset_name 
select struct (struct(fname as fName, lname as lName, phoneNumber as phoneNumber) as personDetails) as _tenantname
from(
select flatten.fName, flatten.lName, flatten.phoneNumber
from(
select explode(_tenantname.personDetails) as flatten from datasetName));

Consideration- The target structure does not have an array, which is the only distinction between the source and target table structures.

Note- For every object in the schema you will have to add an struct and the struct should be aliased with the object name.

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

Regards,

Kumar Saurabh

[BellaSnow5378]

Hi @Kumar_Saurabh_ 

Thank you for your response. Actually, the target schema and source schema have same  structures (array object). I have tried the query you provided, but I'm encountering errors. Could you please assist me in correcting this? and also specify Do i need to give path of the attribute or alias of attribute is enough like u provide ?

Query : Insert into datasetname select struct( individualId as individualId, struct(fName as fName, lName as lName, phoneNumber as phoneNumber) as personDetails) as _tenantname
from(
select flatten.fName, flatten.lName, flatten.phoneNumber
from(
select explode(_tenantname.personDetails) as flatten from dataset name));

After making the necessary changes according to my requirements and obtaining the results, but an error occurs while inserting those records into another dataset. 

2 Error:

[Kumar_Saurabh_]

Hi @BellaSnow5378 ,
 The below query will suffice the need :-

Insert into datasetname
with flatten_table AS
(select explode(_tenantname.personDetails) as flatten, _tenantname.individualId from dataset name)

select struct(struct (individualId as individualId), personDetails as personDetails) as _tenantname
from(
select collect_list(struct(flatten.fName, flatten.lName, flatten.phoneNumber)) as personDetails, individualId from flatten_table);

Regards,

Kumar Saurabh