I have a very large JSON string, I am trying to read using SQL for some report, but the JSON has objects embedded within in at least couple of levels, can somebody please suggest me how can I read all the Data using one SQL query using OpenJson.
I tried in the following way
DECLARE @json NVarChar(max) =
(select top 1 Blob
from Vehicles
where Entity = 'Case' and EntityId = 20)
SELECT * FROM OpenJson(@json);
The result is looking like this I am pasting just one row, but many rows like this exist, any help would be very helpful - thanks in advance.
{
"InputFieldId":500,
"Name":"CA Port of Intermodal Location",
"Description":null,
"InputFieldEntity":"VehicleField",
"InputFieldEntityId":-1,
"Group":"Drayage",
"Type":"dropdown",
"Order":710,
"IsDeleted":false,
"IsRequired":false,
"InputDropdowns":[
{
"InputDropdownId":144,
"InputField":null,
"InputFieldId":500,
"Name":"Crocket C&H Sugar Port",
"IsDeleted":false,
"Order":10
},
{
"InputDropdownId":145,
"InputField":null,
"InputFieldId":500,
"Name":"Hueneme Port",
"IsDeleted":false,
"Order":20
},
{
"InputDropdownId":146,
"InputField":null,
"InputFieldId":500,
"Name":"Long Beach Port",
"IsDeleted":false,
"Order":30
},
{
"InputDropdownId":147,
"InputField":null,
"InputFieldId":500,
"Name":"Oakland Port",
"IsDeleted":false,
"Order":40
},
{
"InputDropdownId":148,
"InputField":null,
"InputFieldId":500,
"Name":"Los Angeles Port",
"IsDeleted":false,
"Order":50
},
{
"InputDropdownId":149,
"InputField":null,
"InputFieldId":500,
"Name":"Redwood City Port",
"IsDeleted":false,
"Order":60
},
{
"InputDropdownId":150,
"InputField":null,
"InputFieldId":500,
"Name":"San Diego Port",
"IsDeleted":false,
"Order":70
},
{
"InputDropdownId":151,
"InputField":null,
"InputFieldId":500,
"Name":"San Francisco Port",
"IsDeleted":false,
"Order":80
},
{
"InputDropdownId":152,
"InputField":null,
"InputFieldId":500,
"Name":"Stockton Port",
"IsDeleted":false,
"Order":90
},
{
"InputDropdownId":153,
"InputField":null,
"InputFieldId":500,
"Name":"West Sacramento Port",
"IsDeleted":false,
"Order":100
},
{
"InputDropdownId":154,
"InputField":null,
"InputFieldId":500,
"Name":"Georgia Pacific Antioch Port",
"IsDeleted":false,
"Order":110
},
{
"InputDropdownId":155,
"InputField":null,
"InputFieldId":500,
"Name":"IMTT Port",
"IsDeleted":false,
"Order":120
},
{
"InputDropdownId":156,
"InputField":null,
"InputFieldId":500,
"Name":"Kindermorgan LA Port",
"IsDeleted":false,
"Order":130
},
{
"InputDropdownId":157,
"InputField":null,
"InputFieldId":500,
"Name":"Kindermorgan Richmond Port",
"IsDeleted":false,
"Order":140
},
{
"InputDropdownId":158,
"InputField":null,
"InputFieldId":500,
"Name":"Koch Carbon Long Beach Port",
"IsDeleted":false,
"Order":150
},
{
"InputDropdownId":159,
"InputField":null,
"InputFieldId":500,
"Name":"Mitsubishi Cement Long Beach. Etc. Port",
"IsDeleted":false,
"Order":160
},
{
"InputDropdownId":160,
"InputField":null,
"InputFieldId":500,
"Name":"UP City of Industry",
"IsDeleted":false,
"Order":170
},
{
"InputDropdownId":161,
"InputField":null,
"InputFieldId":500,
"Name":"UP ICTF",
"IsDeleted":false,
"Order":180
},
{
"InputDropdownId":162,
"InputField":null,
"InputFieldId":500,
"Name":"UP LATC",
"IsDeleted":false,
"Order":190
},
{
"InputDropdownId":163,
"InputField":null,
"InputFieldId":500,
"Name":"UP Lathrop",
"IsDeleted":false,
"Order":200
},
{
"InputDropdownId":164,
"InputField":null,
"InputFieldId":500,
"Name":"UP LA",
"IsDeleted":false,
"Order":210
},
{
"InputDropdownId":165,
"InputField":null,
"InputFieldId":500,
"Name":"UP Oakland",
"IsDeleted":false,
"Order":220
},
{
"InputDropdownId":166,
"InputField":null,
"InputFieldId":500,
"Name":"BNSF Oakland",
"IsDeleted":false,
"Order":230
},
{
"InputDropdownId":167,
"InputField":null,
"InputFieldId":500,
"Name":"BNSF LA",
"IsDeleted":false,
"Order":240
},
{
"InputDropdownId":168,
"InputField":null,
"InputFieldId":500,
"Name":"BNSF Richmond",
"IsDeleted":false,
"Order":250
},
{
"InputDropdownId":169,
"InputField":null,
"InputFieldId":500,
"Name":"BNSF San Bernardino",
"IsDeleted":false,
"Order":260
},
{
"InputDropdownId":170,
"InputField":null,
"InputFieldId":500,
"Name":"BNSF Stockton",
"IsDeleted":false,
"Order":270
}
],
"InputValue":{
"InputValueId":0,
"InputField":null,
"InputFieldId":500,
"InputValueEntity":"Vehicle",
"InputValueEntityId":0,
"Value":null,
"InputDropdown":null,
"InputDropdownId":null,
"DateCreated":"0001-01-01T08:00:00Z",
"DateUpdated":"0001-01-01T08:00:00Z",
"CreatedByUserId":null,
"UpdatedByUserId":null,
"CreatedByUser":null,
"UpdatedByUser":null
},
"isHidden":false
}
I am looking for a Query that can show me all the data into one table format, I am gets into all the subsections of the string and puts into one table result. any help please, even if the rows has repeated values as some parent object values would be repeated for child objects. It should be a general query to run for any jSon string, because here we don't know how many objects are there nested.
The query has to give me columns and rows dynamically, depending upon the depth and number of fields in jSon. For example, if Continent, Country, States and Cities, so for each City the State, Country and Continent fields would be repeated, but the thing is, we don't know the depth and columns exactly, it might change for every jSon string, the Query must be able to run for all the jSon strings without fail - any help my friend?