Json Path + Ungroup not skipping null array, resulting in mis-aligned data set

[alabamian2]

Hi KNIME experts, hope you are doing well.

I am attempting to parse json data that’s in one of the field in each of the records. I have attached a simplified dataset in a Knime workflow project file, but I have several hundred thousands of records I need to process.

This is what the simplified data looks like.

After Ungrouping, the first row should not have any values in the last 3 cols since it does not contain any values (or null) ==> “DataValue”:null And those values all need to shift down by 1 row.

I have experimented with different settings or parsing that nested “DataValue”: separately but I cannot get the final data set to look correct. How could I get the data to skip (put ? null) when the data does not exist or null?

Thank you very much in advance for your time and help. I’ll keep working on this as well. THANKS and have a nice day!!

KNIME_JSON with null array.knwf (24.9 KB)

[duristef]

hi @alabamian2 ,
I’ve split the workflow in two. Maybe it can solve the problem
KNIME_JSON with null array.knwf (18.8 KB)

[alabamian2]

Hi @duristef ,
Aaaaahhhhhh, that’s how you do it. I have attempted to take a similar approach with joins but couldn’t get it to work. Clever and super appreciated. I am going to incorporate this into the workflow with real and bigger data and see what happens. I’ll report back the result.

I really appreciate your time and help @duristef !! I hope you have a wonderful rest of the day and week. THANK YOU!!!

[duristef]

Glad to be helpful! I’m goin’ to spend “the rest of the day” in bed, 'cause here in Italy it’s 11:56PM, so good night!