Extracting Json fields onto columns

Good day everyone,

I tried to extract fields from a Json file with Json Path, Ungroup, and Cell Splitter without success after going over quite a few previous posts in this community. I was wondering if I could have some advice please. On the file, I extract “id”, “timestamp”, and “data”, but when I ungroup/extract them, “id” and “timestamp” are not duplicating for every row based on every child within “data”.

My goal is to have a final table with 5 columns like below with made up data just for simplicity, but a sample is attached:
id timestamp field label value
1 1:01 111 A err
1 1:01 123 B qwe
1 1:01 142 C sde
2 2:12 111 A azx
2 2:12 123 B thd
3 …
3 …
3 …
3 …

I would appreciate some help. Thank you very much in advance!

Sample data getting into a Json Path follows:
{
“submissions” : [ {
“id” : “391061650”,
“timestamp” : “2018-03-09 14:31:34”,
“data” : {
“59762154” : {
“field” : “59762154”,
“value” : “{\r\n “StudentRecordColumnName”: “FormFieldLabel”,\r\n “FirstName”: “Child Name”,\r\n “LastName”: “Child Name”,\r\n “BirthDate”: “Date of Birth”,\r\n “Gender”: “Gender”,\r\n “StartDate”: “What is your child’s start date?”,\r\n “Program”: “Program Selection”,\r\n “FullOrPartTime”: “Program Selection”\r\n}”,
“label” : “Field Mappings”,
},
“62028989” : {
“field” : “62028989”,
“value” : “San Francisco”,
“label” : “Please indicate the campus you will be attending.”,
},
“59762160” : {
“field” : “59762160”,
“value” : “Female”,
“label” : “Gender/Gender Identity”,
},
“59762162” : {
“field” : “59762162”,
“value” : “Word of mouth or referral”,
“label” : “How did you hear about us?”,
}
}
}, {
“id” : “394673003”,
“timestamp” : “2018-03-21 19:27:43”,
“data” : {
“59762154” : {
“field” : “59762154”,
“value” : “{\r\n “StudentRecordColumnName”: “FormFieldLabel”,\r\n “FirstName”: “Child Name”,\r\n “LastName”: “Child Name”,\r\n “BirthDate”: “Date of Birth”,\r\n “Gender”: “Gender”,\r\n “StartDate”: “What is your child’s start date?”,\r\n “Program”: “Program Selection”,\r\n “FullOrPartTime”: “Program Selection”\r\n}”,
“label” : “Field Mappings”,
},
“62028989” : {
“field” : “62028989”,
“value” : “Marte”,
“label” : “Please indicate the campus you will be attending.”,
},
“59762160” : {
“field” : “59762160”,
“value” : “Male”,
“label” : “Gender/Gender Identity”,
},
“59762162” : {
“field” : “59762162”,
“value” : “Word of mouth or referral”,
“label” : “How did you hear about us?”,
},
“59762196” : {
“field” : “59762196”,
“value” : “Mar 21, 2019”,
“label” : “Submitted Date”,
}
}
}, {
“id” : “396691053”,
“timestamp” : “2018-03-29 14:16:07”,
“data” : {
“59762154” : {
“field” : “59762154”,
“value” : “{\r\n “StudentRecordColumnName”: “FormFieldLabel”,\r\n “FirstName”: “Child Name”,\r\n “LastName”: “Child Name”,\r\n “BirthDate”: “Date of Birth”,\r\n “Gender”: “Gender”,\r\n “StartDate”: “What is your child’s start date?”,\r\n “Program”: “Program Selection”,\r\n “FullOrPartTime”: “Program Selection”\r\n}”,
“label” : “Field Mappings”,
},
“62028989” : {
“field” : “62028989”,
“value” : “LA”,
“label” : “Please indicate the campus you will be attending.”,
},
“59762160” : {
“field” : “59762160”,
“value” : “Female”,
“label” : “Gender/Gender Identity”,
},
“59762162” : {
“field” : “59762162”,
“value” : “Word of mouth or referral”,
“label” : “How did you hear about us?”,
},
“59762196” : {
“field” : “59762196”,
“value” : “Mar 21, 2020”,
“label” : “Submitted Date”,
}
}
} ],
“total” : 3,
“pages” : 1,
“pretty_field_id” : “59762158”
}

Hi @gccrafael and welcome to the Knime Community.

The JSON string you gave was not properly formatted and therefore is an invalid JSON as is. You can see in the first array the \r\n as string as part of the JSON. And then you are using these quotes “ and ” as opposed to ". Lastly, there were misplaced quotes at 6 places.

I had to spend time fixing the given JSON string, and I almost gave up on it and move to another thread. That would have been too bad because I do have a workflow that does what you need.

In the future, please pay attention to these details to make sure that we have a working copy of the data, and we can immediately jump into working on the solution instead of having to fix the data first. Basically help us help you :slight_smile:

So, this is the input data (same as the sample you gave, but with the JSON formatted properly):

Here’s the result:

I kept the “value” column as JSON as it looks like there might be JSON results in that column. You can always convert to string if needed.

And here’s the workflow:
Extracting JSON fields into columns.knwf (19.9 KB)

4 Likes

Hi @bruno29a,

I apologize for missing the unformatted data.

I really appreciate your help, and I will be more careful next time. It worked beautifully.

Thank you very much!

3 Likes

No problem @gccrafael

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