Trying to read JSON string using SQL Query and OpenJson

Question

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?

As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. — Dale K, Jul 15 '21 at 23:50
Sorry I didn't know about that, I will make sure next time thank you so much — AbdulAleem, Jul 16 '21 at 00:06
@Dale do you suggest me to delete the image now buddy? Now its done — AbdulAleem, Jul 16 '21 at 00:09
And add it back as formatted text - at least some of it - unlikely its all needed. — Dale K, Jul 16 '21 at 00:11
Whitespace is free, you know. There's a bunch of nested JSON here, how do you want your results to look? — Charlieface, Jul 16 '21 at 00:44
@AbdulAleem, this [Q&A](https://stackoverflow.com/questions/61800927/read-any-json-into-list-of-key-value-pairs-eav-format-in-sql-server/61800928#61800928) shows how to read any JSON into list of key-value pairs (EAV format) in SQL Server. — Zhorov, Jul 16 '21 at 08:49
@Venkataraman, that's the problem it 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 field 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? — AbdulAleem, Jul 16 '21 at 19:18
@AbdulAleem, for the current JSON, please put the expected output. It will clarify. Based on the answer, you can extrapolate for your needs — Venkataraman R, Jul 19 '21 at 03:36

ch_g · Answer 1 · 2021-07-16T01:48:51.497

When you use openjson to parse out columns in the root level, the embedded array should be parsed out as json column. Then you can further parse this json column using openjson again. Please see how col_2 is parsed in the example.

For embedded objects on the same level, you can directly parse their columns out by specifying field path in openjson. Please see how col_3 is parsed in the example.

Here is the example -

declare 
  @json nvarchar(max) = '[
      {"col_1":"c1-1", 
       "col_2":[{"col_2_1":"c21-1-1","col_2_2":"c22-1-1"},{"col_2_1":"c21-1-2","col_2_2":"c22-1-2"}],
       "col_3":{"col_3_1":"c31-1", "col_3_2":"c32-1"}
      },
      {"col_1":"c1-2", 
       "col_2":[{"col_2_1":"c21-2-1","col_2_2":"c22-2-1"},{"col_2_1":"c21-2-2","col_2_2":"c22-2-2"}],
       "col_3":{"col_3_1":"c31-2", "col_3_2":"c32-2"}
      }
  ]';

with d1 as (
select *
  from openjson(@json) with (
         col_1 nvarchar(max)
        ,col_2 nvarchar(max) as json  
        ,col_3_1 nvarchar(max) '$.col_3.col_3_1'
        ,col_3_2 nvarchar(max) '$.col_3.col_3_2'
       )
)
select d1.col_1
      ,d1.col_3_1
      ,d1.col_3_2
      ,d2.col_2_1
      ,d2.col_2_2
  from d1 cross apply openjson(d1.col_2) with (col_2_1 nvarchar(max), col_2_2 nvarchar(max)) d2

The output will be -

c1-1    c31-1   c32-1   c21-1-1   c22-1-1
c1-1    c31-1   c32-1   c21-1-2   c22-1-2
c1-2    c31-2   c32-2   c21-2-1   c22-2-1
c1-2    c31-2   c32-2   c21-2-2   c22-2-2

You need to make sure data obtained in this way make sense to your business.

Thank you so much, is there anyway to write without mentioning the Column names like Col1 etc, just with generic SQL Statement like * etc or get the column names dynamic way, because the column names are not same or number of columns might change. — AbdulAleem, Jul 16 '21 at 07:58
Technically, yes, it is possible, but will be quite complicated, you will end up writing something like a json parser. The key point here is that you have to know or find out the structure of the source json in order to extract all columns in a flat structure. — ch_g, Jul 16 '21 at 19:23
OK if we know the commonly all number of columns in general in different json strings that we have but not particularly in this json file/string, can we process it at least because we know all the columns used for generating these files. — AbdulAleem, Jul 21 '21 at 16:03

Trying to read JSON string using SQL Query and OpenJson

1 Answers1