Parsing Json with dynamic Keys

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)

1 Like

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:

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)

:blush:

2 Likes

Hi @armingrudd,

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

1 Like

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

2 Likes

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/

:blush:

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.