1

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?

10
  • 1
    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
    Commented Jul 15, 2021 at 23:50
  • Sorry I didn't know about that, I will make sure next time thank you so much
    – AbdulAleem
    Commented Jul 16, 2021 at 0:06
  • 1
    No reason to wait... edit it now...
    – Dale K
    Commented Jul 16, 2021 at 0:06
  • @Dale do you suggest me to delete the image now buddy? Now its done
    – AbdulAleem
    Commented Jul 16, 2021 at 0:09
  • And add it back as formatted text - at least some of it - unlikely its all needed.
    – Dale K
    Commented Jul 16, 2021 at 0:11

1 Answer 1

Reset to default
2

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.

3
  • 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
    Commented Jul 16, 2021 at 7: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
    Commented Jul 16, 2021 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
    Commented Jul 21, 2021 at 16:03

Not the answer you're looking for? Browse other questions tagged or ask your own question.