Need to populate the object array fields | Community
Skip to main content
B
BellaSnow5378
Level 2
April 4, 2024
Solved

Need to populate the object array fields

  • April 4, 2024
  • 1 reply
  • 1250 views

Hi, @arpan-garg @kumar29917170hcyp @abhinavbalooni @ssj @_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  

 

    This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
    Best answer by Kumar29917170hcyp

    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-structures

    Regards,

    Kumar Saurabh

     

    1 reply

    K
    Adobe Employee
    Kumar29917170hcypAdobe EmployeeAccepted solution
    Adobe Employee
    April 4, 2024

    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-structures

    Regards,

    Kumar Saurabh

     

      B
      BellaSnow5378Author
      Level 2
      April 4, 2024

      Hi @kumar29917170hcyp 

       

      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:

       

       

        K
        Adobe Employee
        Kumar29917170hcypAdobe Employee
        Adobe Employee
        April 5, 2024

        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

          Terms & ConditionsAccessibility statement

          Loading footer...