JSON Parse To Columns
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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 :)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
![](/skins/images/1AD6617C767659D8A9F9801BEDFCFB20/responsive_peak/images/icon_anonymous_message.png)