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”
}