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. 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.
{
"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 should get all the subsections of the string and put into one table result. 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 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 columns that one particular json file has but we know that they are all generated from a set of columns only. so I want a SQL query that checks if value for a particular column of a table in exists in the jSon. if not it gives value as 'Value Not Exists', if exists puts that value, all the columns are there in a table in the Database as 'InputColumnName', 'Type' and 'InputFieldEntity'. some of the column names here are Vehicle Owner, Vehicle Operator, Date, Idling Start Time, Idling Stop Time, License Plate, State of Registration, VIN, Owner ID, Vehicle Make/Model.
