JSON Path node. Problem with list in list extraction

[dbolshev]

Hi, all.

I have a problem with extraction data from JSON to plain table using JSON Path node.
I have such JSON:
[
{
“key1”: “value1”,
“key2”: [“item1”, “item2”]
},
{
“key1”: “value2”,
“key2”: [“item3”, “item4”]
}
]
So I have a list of objects and inside each object I also have a list in “key2”.
I’ve used “Add collection query” to extract all values from “key1” and “key2”:

As result I’ve got table:

There is no information which “items” and “values” were extracted from same objects. So I can’t use Ungroup node and get a result like this:

rowID key1 key2
0 value1 item1
1 value1 item2
2 value2 item3
3 value2 item4

Does anybody can help with this problem?

[ArjenEX]

Hi @dbolshev

In cases like this it’s best to do it in multiple steps. The trick is here to extract key2 first as json rather than string like you have now.

This will maintain the relationship between the keys and get the full content of key2. Ungroup the columns, then query key2 again and ungroup those also.

Final table

[dbolshev]

Hi, @ArjenEX

Thanks a lot for your help!
It works)