JSON file not reading correctly

[Page0727]

I am working on a test JSON file and have encountered some issues. Column data is not aligning correctly. For example row 36, column 9 is saying RESPONSIBLE_PERSON, when reviewing the JSON data this is incorrect for this particular row. I am not sure whether there is a step I am missing here, any help would be appreciated.

https://www.dropbox.com/sh/y0233uc9hmr08py/AAC_B4umBWc6fskZh9HgJX_La?dl=0

Thanks

[SamirAbida]

Hello @Page0727,

It looks fine to me. Indeed, if you look into your second “Json Path” node, you’ll see that “you chose” to show this particular column :

i.e this field is populated with " RESPONSIBLE_PERSON". Isn’t it what you are looking for ?
What are you expecting ?

Br,
Samir

[Page0727]

I was seeing the responsible person under the actual responsible person in row 18, I made an assumption which was not a good idea considering this is just a test file.

[SamirAbida]

@Page0727,

no problem at all. So there is no issue after all, correct ?
Br,

Samir

[Page0727]

Correct. Although I did have a question which maybe you can help me with.

When I am selecting the Person tracking ID I am using the ‘Add Collection Query’ option to display the data correctly, however, when it comes to the address fields I am having to use the ‘Add Single Query’ option to display this data correctly. It is a little confusing to me.

Thanks for all your help on this, it was good to have a second set of eyes look at this.

[SamirAbida]

@Page0727,

Explanation :

“The result of a simple query (also called definite JSONPath) is a single value. The result of a collection query (also called indefinite JSONPath) is a list of multiple values.”

Basically, imagine that (for each record ie each line) you have multiple “personTrackingNumber”, it will be nested and to retrieve that in Json you should use :

$['coveredInsuredMembers'][0]['personTrackingNumber']
$['coveredInsuredMembers'][1]['personTrackingNumber']
$['coveredInsuredMembers'][2]['personTrackingNumber']

etc …

By using “Add collection query”, Knime will use $[‘coveredInsuredMembers’][*][‘personTrackingNumber’] and will concat all “personTrackingNumber” (ie record [0] recor [1] etc…) as a list in a column.

So to use it correctly, when you use the option :

• Add single query, the column is populated with one “data” (notice that the list column is not checked)
• Add collection query, the column is populated with a list (that you should “ungroup” as multiple columns or so, and the list column is checked)

So in most of the case you should use “Add single query”, unless you have multiple records, then you should use a collection.

When is use “add single query” for “personTrackingNumber” it works nice :

Br,
Samir

[Page0727]

Thanks Samir! This is a great explanation. Although this is not directly the answer to the initial question I am going to mark this as the answer as I think this will be the most useful info to others.

[SamirAbida]

@Page0727,

Glad I could help ! Happy “Knim’ing”.

Cheers,
Samir