A few months ago, I asked for help reading a CSV with a JSON field and you guys came up with a solution that worked perfectly... now I got an even bigger problem. The fields in the JSON are more complex:
Cat_ID, JSON
1234,{"Name":"Buttons","Age":3,"Likes":[{"Item":"Food","Name":"Tuna"},{"Item":"Food","Name":"Chicken"},{"Item":"Toy","Name":"Laser Pointer"}],"Dislikes":[{"Item":"Food","Name":"Lemons"},{"Item":"Cat","Name":"Mittens"}],"Color":"Orange"}
1121,{"Name":"Mittens","Age":5,"Dislikes":[{"Item":"Cat","Name":" Buttons"}],"Color":"Black"}
The first record has multiple sub fields in the Likes and Dislikes categories. The second record has just 1 Dislike. There could be 0 to many Likes and Dislikes. Also every field in each category uses the same field name. So I can't just brute force combine whatever is before a colon to whatever is after.
Ideally I want to end up with 3 tables:
Details that contain: Cat_ID, Name, Age and Color.
Likes that contain: Cat_ID, Item and Name
Dislikes that contain: Cat_ID, Item and Name
Is there a way to parse by commas, but using the square brackets are delimiters?
... View more