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

Question

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 Answer

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 · Answer

@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!

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded