Parsing Json with dynamic Keys

[sudheerkopparapu]

Hello Members,

I need your help in parsing a json file to table. I have attached input.json and expected output.

The main problem here is name of the ‘keys’ are not constant (Example route ID and trip ID are made as keys and they are dynamic).
How to call out these values into a table using JSONpath or JsonToTable nodes in knime when we have a varying key names.

Thanks
Sudheerinput.json (4.0 KB) Expected Output.xlsx (9.4 KB)

[armingrudd]

Hi @sudheerkopparapu,

First of all, the JSON file you have provided has an extra comma at line 189.

There was a similar topic like this, where some manipulation on an XML was needed to transform keys (element names) to values:

Creating column names with XPATH Node

Here I have an alternative solution which is a bit tricky: First, use a String Manipulation node to modify your XML file to make the element names be values. E.g.: If this is the XML: <?xml version='1.0' encoding='UTF-8'?> <root> <item> <id>0001</id> <idBoard>0001</idBoard> </item> <item> <id>0001</id> <idBoard>0001</idBoard> </item> <item> <id>0001</id> <idBoard>0001</idBoard> </item> </root> Use this expression: rege…

So I followed the same idea to manipulate the JSON using the String Manipulation node and this expression:

regexReplace(regexReplace($json$, "\"(\\d[^\"]+)\"", "\"item\":\"$1\",\n\"item2\""), "([-\\d.]+)", "$1")

And then by using 3 JSON Path nodes, I extracted each level separately passing the sub-level as json to the next JSON Path node.
Here is the workflow:
22342-1-1.knwf (45.3 KB)

[sudheerkopparapu]

Hi @armingrudd,

Thanks for your response. Let me analyze your workflow and come back to you. Many thanks !

[sudheerkopparapu]

Hi,
This works perfectly and does the exact job required. Thanks a lot. By the way could you please suggest regex reference material for Knime?

Thanks
Sudheer

[armingrudd]

Glad to know it worked for you.

Here are some resources to start using regex:
https://blog.statinfer.com/regex-in-knime-what-a-functionality-booster/
https://www.regular-expressions.info/