JSON parse

[Data_consumer]

Hi Community,

Any hints how to parse (nested?) JSON into a table? Currently, I only get the name and the securitycode to the first row, and the other rows only contain the obsdate and close and come without these name and securitycode columns… I have used the JSON path component, so that I add all these 4 datapoints as collection querys… whats the correct way to do this?

{
“data” : {
“security” : [ {
“name” : “test1”,
“securityCode” : “tst1”,
“History” : [ {
“close” : 116.02654,
“obsDate” : “2020-03-31”
}, {
“close” : 117.75992,
“obsDate” : “2020-06-30”
}, {
“close” : 113.68442,
“obsDate” : “2020-09-30”
}, {
“close” : 116.03309,
“obsDate” : “2020-12-31”
}, {
“close” : 118.53721,
“obsDate” : “2021-03-31”
}, {
“close” : 118.9464,
“obsDate” : “2021-06-30”
}, {
“close” : 122.32992,
“obsDate” : “2021-09-30”
}, {
“close” : 125.9209,
“obsDate” : “2021-12-31”
}, {
“close” : 128.45948,
“obsDate” : “2022-03-31”
}, {
“close” : 124.24969,
“obsDate” : “2022-06-30”
}, {
“close” : 122.573,
“obsDate” : “2022-09-30”
}, {
“close” : 119.54335,
“obsDate” : “2022-12-30”
}, {
“close” : 117.05316,
“obsDate” : “2023-03-31”
}, {
“close” : 112.71864,
“obsDate” : “2023-06-30”
}, {
“close” : 110.93365,
“obsDate” : “2023-09-29”
}, {
“close” : 102.69651,
“obsDate” : “2023-12-29”
} ]

[ArjenEX]

Hi @Data_consumer

What’s your desired output format?

[Data_consumer]

@ArjenEX

at’s your desired output form

@ArjenEX I want to output it as a a table

[ArjenEX]

That was clear, but in terms of columns and rows Since it’s nested it will always be a bit skewed with some rows that will have to be duplicated etc.

This is one way:
Query the name, security code and full History as JSON.

Then from the History JSON, query the closes and obsDates

Followed by the ungrouping of these:

[Data_consumer]

@ArjenEX

t in terms of columns and ro

@ArjenEX aa, sorry Name, securitycode, Close, obsdate

so each close and obsdate as own row with the name and security code

[ArjenEX]

Allright, see above. I amended my post

[Data_consumer]

@ArjenEX

query the closes and obsDates

Thanks! I will test this and come back

[Data_consumer]

@ArjenEX

is is one way:
Query the name, security code and full History as JSON.

Hi @ArjenEX! Sorry, but I think I gave a bad example I have multiple security elements within the JSON and it seems not to be working. Or the other option is that I have just misconfigured something… If it should work with multiple, could you send the example model?

The example with multiple sections below.
{
“data” : {
“security” : [ {
“name” : “test1”,
“securityCode” : “tst1”,
“History” : [ {
“close” : 116.02654,
“obsDate” : “2020-03-31”
}, {
“close” : 117.75992,
“obsDate” : “2020-06-30”
}, {
“close” : 113.68442,
“obsDate” : “2020-09-30”
}, {
“close” : 116.03309,
“obsDate” : “2020-12-31”
}, {
“close” : 118.53721,
“obsDate” : “2021-03-31”
}, {
“close” : 118.9464,
“obsDate” : “2021-06-30”
}, {
“close” : 122.32992,
“obsDate” : “2021-09-30”
}, {
“close” : 125.9209,
“obsDate” : “2021-12-31”
}, {
“close” : 128.45948,
“obsDate” : “2022-03-31”
}, {
“close” : 124.24969,
“obsDate” : “2022-06-30”
}, {
“close” : 122.573,
“obsDate” : “2022-09-30”
}, {
“close” : 119.54335,
“obsDate” : “2022-12-30”
}, {
“close” : 117.05316,
“obsDate” : “2023-03-31”
}, {
“close” : 112.71864,
“obsDate” : “2023-06-30”
}, {
“close” : 110.93365,
“obsDate” : “2023-09-29”
}, {
“close” : 102.69651,
“obsDate” : “2023-12-29”
} ]
}, {
“name” : “test2”,
“securityCode” : “tst2”,
“History” : [ {
“close” : 116.02654,
“obsDate” : “2020-03-31”
}, {
“close” : 117.75992,
“obsDate” : “2020-06-30”
}, {
“close” : 113.68442,
“obsDate” : “2020-09-30”
}, {
“close” : 116.03309,
“obsDate” : “2020-12-31”
}, {
“close” : 118.53721,
“obsDate” : “2021-03-31”
}, {
“close” : 118.9464,
“obsDate” : “2021-06-30”
}, {
“close” : 122.32992,
“obsDate” : “2021-09-30”
}, {
“close” : 125.9209,
“obsDate” : “2021-12-31”
}, {
“close” : 128.45948,
“obsDate” : “2022-03-31”
}, {
“close” : 124.24969,
“obsDate” : “2022-06-30”
}, {
“close” : 122.573,
“obsDate” : “2022-09-30”
}, {
“close” : 119.54335,
“obsDate” : “2022-12-30”
}, {
“close” : 117.05316,
“obsDate” : “2023-03-31”
}, {
“close” : 112.71864,
“obsDate” : “2023-06-30”
}, {
“close” : 110.93365,
“obsDate” : “2023-09-29”
}, {
“close” : 102.69651,
“obsDate” : “2023-12-29”
} ]
} ]
}
}

[ArjenEX]

I see @Data_consumer. Same principle applies, you just have to take two additional steps to seperate each security element before doing further processing

See WF:
JSON parse.knwf (27.9 KB)

[Data_consumer]

@ArjenEX You are a star, works like a charm now!! A big thanks for the help! :=)