Dealing with Missing Values in JSON (JSON to Table, Aggregator, Ungroup)

KNIME Analytics Platform
1

So! I have some JSON and right now I can get everything I want into columns and rows using the List option in Column Aggregator and/or JSON Path.

The problem is that in my JSON (comes from an API) some values are missing, so when I list, there aren't as many values in one column as there are in others and they don't match.

Is there any way I'm missing to make this work how I want it to?

Ali

2

Hi Ali,

if a value is missing in the JSON, the output will be a missing value. So your workflow is still working. You might want to use a Missing Value node afterwards to assign some defaults. 

Best regards, Iris 

3

I created an account to respond to this as it was bugging me. 

 

Say the list is in "Issues"->"Fields"->"ID" in the JSON, where the ID is the field you are getting in a list.

- In the "JSON Path" node, bring back the "Fields" level ($['issues'][*]['Fields'])

- and make it return a JSON (Edit JSONPath->Result Type).

- UNGROUP the data to unpack the other lists that contain no missing values, then parse the JSON field with "JSON to Table" Node to unpack the fields with the missing values.

 

The result will give a "?" to the missing columns and they will sync up

1 Like