Converting Objects with arrays into row format for DB storage

Designing Pipelines
, , ,
#1

Hi, I have issue converting an object with array to a format that could be inserted into a table.
I have a following dynamic JSON document:

“testcode”: {
“1203”: [1,2,5,6,8,10,11,12,13,14,16,17],
“1205”: [9],
“1206”: [7,3,4,15]
}

Above, testcode is shown to be an object that contains array 1203, 1205, 1206. However these value changes so it is not really a fixed description. What I want to do in the above data is to store like this. See below.
Using 1203, I want to see this format which contains two fields:
1203,1
1203,2
1203,5
1203,6
1203,8
1203,10
1203,11
1203,12
1203,13
1203,14
1203,16
1203,17

Is this possible? If not will store it as variant instead.

Thanks,
David

#2

You can do this with Python code in a Script Snap. I’ve added the Python here: https://pastebin.com/9Nf2aqew

Please let me know if you need any assistance with it.

-John.

#3

Hello @davidm,

I’ve attached a pipeline that will get you the next result:

image

Transforming_Document_2021_03_18.slp (5.9 KB)

I hope you’ll find it helpful,

Regards,

Bojan Velevski

1 Like