JSON Parse To Columns

[pomaaa]

Hi,

I have a table that has a JSON in one of the columns, and I want to parse the fields from JSON to columns. The issue that I have is that the JSON doesn't have all the fields written inside of it for each row. For example, in some cases it has all the categories and values, in some cases it only has some of them. If there aren't those categories or values, they should appear in a column, but with blank value.

Just to explain it more clearly, this is an example of the table that I have:

ID	Name	Type	JSON
1	Bob	True	{"Category 1": { "Text1" : "Text1 value", "Text2": "Text 2 value"}, "Category 2" : { "Text2" : "Text2 Value"}}
2	James	False	{"Category 2": { "Text2": "Text 2 value"}}
3	Austin	True	{"Category 1": { "Text2": "Text 2 value"}, "Category 2" : { "Text1" : "Text1 Value"}}

And this is what I want to accomplish:

ID	Name	Type	JSON	Category1.Text1	Category1.Text2	Category2.Text1	Category2.Text2
1	Bob	True	{"Category 1": { "Text1" : "Text1 value", "Text2": "Text 2 value"}, "Category 2" : { "Text2" : "Text2 Value"}}	Text1 Value	Text2 Value	Null	Text2 Value
2	James	False	{"Category 2": { "Text2": "Text 2 value"}}	Null	Null	Null	Text2 Value
3	Austin	True	{"Category 1": { "Text2": "Text 2 value"}, "Category 2" : { "Text1" : "Text1 Value"}}	Null	Text2 Value	Text1 Value	Null

I've tried lots of things but haven't managed to find a solution, because in all of the cases the JSON has the same amount of fields. Does anybody have a solution for this? :)

Thank you very much.

[binuacs · Accepted solution]

@pomaaa you can try the JSON parse tool and cross tab the result

[pomaaa]

Great, thanks for the input. I had a large csv and got notifications that over 1000 new columns are created so I thought it didn't work but it does, I just need to remove some columns before :)

[syedsafdar9791]

I want to parse the below json field. Can you please help

</VehicleRegExpiryDate><VehicleCylinders>4</VehicleCylinders><VehicleWeight>1500</VehicleWeight><VehicleLoad>5</VehicleLoad><VehicleValue>30000</VehicleValue><VehicleOwnerTransfer>false</VehicleOwnerTransfer><DeductibleValue /><VehicleAgencyRepair>false</VehicleAgencyRepair><VehicleEngineSizeCode>1</VehicleEngineSizeCode><VehicleUseCode>1</VehicleUseCode><VehicleMileage /><VehicleTransmissionTypeCode>2</VehicleTransmissionTypeCode><VehicleMileageExpectedAnnualCode>1</VehicleMileageExpectedAnnualCode><VehicleAxleWeightCode /><VehicleOvernightParkingLocationCode>1</VehicleOvernightParkingLocationCode><VehicleModification>false</VehicleModification><VehicleModificationDetails /><NCDFreeYears>1</NCDFreeYears><NCDReference>NCD211023</NCDReference><VehicleSpecifications /><Drivers><DriverTypeCode>1</DriverOccupation><DriverDrivingPercentage>100</DriverDrivingPercentage><DriverEducationCode>4</DriverEducationCode><DriverMedicalConditionCode>1</DriverNCDReference><DriverLicenses><LicenseCountryCode>113</LicenseCountryCode><LicenseNumberYears>5</LicenseNumberYears><DriverLicenseTypeCode>3