JSON nestled reponse parses into rows

[Brayndasilva]

Hi,

I am collecting metadata on a certain dataset from a public API. The metadata is provided to me in JSON format.

https://opendata.cbs.nl/ODataApi/odata/83648NED/RegioS

With the help of the 'Download' tool I fetch the JSON response and then parse it with the 'JSON Parse' tool. The data is there, but not quite in the right format. Refer to the below image.

The data I need is nestled within the 'value' group with each 'Key' containing the information I need. I am trying to generate a table containing the values of each Key nestled in the JSON response. So for the first line something like:

Key	Title	Description	CategoryGroupID
NL01	Nederland		NULL
..	..	..	..

What would be an approach to transform the data into the desired format?

[atcodedog05 · Accepted solution]

Hi @Brayndasilva 

Here is how you can do it. You can use regex to extract rowid and column names. And crosstab to convert it to table.

Workflow:

Hope this helps : )