Converting flattened array object to nested arrays using sql query in adobe experience platform

[RitwikMo]

I have two 2 datasets.

1st dataset structure is as follows:

_spnew object

    CoolestNum  Integer

    FavChoice  String

    airB_UID   String

Output is as follows:

FavChoice  CoolestNum  airB_UID  

t                      17                          b_333

t                      18                          b_333

t                      19                          b_333

t                      421                       b_111

2nd dataset structure is as follows:

_spnew object

    Array Of Objects object[]

    CoolestNum  Integer

   FavChoice  String

airB_UID   String

Output is as follows:

airB_UID    arrayOfObjects

b_333        {(7,ic),(8,ic),(9,t)}

b_111        {(420,ic)}

b_333       {(7,ic),(8,ic),(9,t)}

b_111       {(420,ic)}

b_333       {(7,ic),(8,ic),(9,t)}

b_111       {(420,ic)}

b_333       {(7,ic),(8,ic),(9,t)}

b_111       {(420,ic)}

I want the output of the 1st dataset to be aligned with the output of the 2nd dataset in terms of format and structure i.e nested arrays using SQL Query.

So how do i write a sql query for the same to convert the object field into nested arrays.

[DavidRoss91 · Accepted solution]

Hi @RitwikMo 

Just trying to better understand what I am seeing here. For the second dataset you should just be able to nest all 3 of your attributes under the array object that you will be using, and your output would be as follows:

arrayOfObjects

{(t, 17, b_333)}

{(t, 18, b_333)}

{(t, 19, b_333)}

{(t, 421, b_111)}

[kautuk_sahni]

@RitwikMo Did you find the suggestion helpful? Please let us know if you require more information. Otherwise, please mark the answer as correct for posterity. If you've discovered a solution yourself, we would appreciate it if you could share it with the community. Thank you!