Return to Question

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?

    DECLARE @json NVarChar(max) = (select top 1 Blob from Vehicles where Entity='Case' and EntityId=20)
  SELECT * FROM OpenJson(@json);

{"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}    

DECLARE @json NVarChar(max) = 
(select top 1 Blob 
from Vehicles 
where Entity = 'Case' and EntityId = 20)

SELECT * FROM OpenJson(@json);

{
   "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.

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.