OPENJSON - parsing nested JSON preserving relations

Question

I have a JSON array that looks like this

[
  {
    "_id": "12345",
    "uniqueId": null,
    "companyName": "ABC Corp",
    "yearFounded": 1958,
    "stateFounded": "Delaware",
    "someField": [
      {
        "primaryCode": "14",
        "secondaryCode": "32",
        "tertiaryCode": "00",
        "description": "Moving Walks"
      },
      {
        "primaryCode": "14",
        "secondaryCode": "40",
        "tertiaryCode": "00",
        "description": "Lifts"
      },
      {
        "primaryCode": "14",
        "secondaryCode": "00",
        "tertiaryCode": "00",
        "description": "Conveying Equipment"
      }
    ],
    "SomeRegionProperty": [
      {
        "region": "Other"
      },
      {
        "region": "MD - Eastern Shore"
      },
      {
        "region": "MD - Southern"
      },
      {
        "region": "MD - Central (incl. Baltimore)"
      }
    ],
    "Markets": [
      {
        "market": "Pharmaceutical & Laboratory"
      },
      {
        "market": "Retail"
      }
    ],
    "SomeEmptyProperty": [],
    "AndYetAnother": [
      {
        "unionName": "Name",
        "unionNumber": "Value 1234"
      }
    ]
  },
  {
    "_id": "949690",
    "companyName": "XYZ Co",
    "yearFounded": 2015,
    "stateFounded": "New York",
    "someField": [
      {
        "primaryCode": "15",
        "secondaryCode": "62",
        "tertiaryCode": "032",
        "description": "test"
      }
    ],
    "SomeRegionProperty": [
      {
        "region": "Other"
      },
      {
        "region": "MD - Eastern Shore"
      },
      {
        "region": "MD - Southern"
      },
      {
        "region": "MD - Central (incl. Baltimore)"
      }
    ],
    "Markets": [
      {
        "market": "Pharmaceutical & Laboratory"
      },
      {
        "market": "Retail"
      }
    ],
    "SomeEmptyProperty": [],
    "AndYetAnother": [
      {
        "unionName": "Name",
        "unionNumber": "Value 1234"
      }
    ]
  }
]

So, I need to distribute the object into sql tables (sql server) With the table "MainTbl" - with the Outer most level fields as columns and _id as primary key. THen tables for each nested part ("SomField", "SomeRegionProperty", "Markets" .. etc..) with _id being a foreign key in those tables.

I understand that openJson returns a table value. But, how can I preserve _id=12345 while inserting 3 records for "someField ? etc...

I want to end up with structure like this

And my JSON can be very large, there can be many related tables with many fields. So, going through OPENJSON on the main input multiple times is not too desirable. It may slow down the process. But, I will take it if there is no other way.

While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). — Yitzhak Khabinsky, Feb 22 '23 at 20:15
what you can do is to create a main temporary import table that also contain the other sub tables as json fragments. this way, for each sub table, you can always access to main data, and also only need to OPENJSON only the fragments you're interested in — siggemannen, Feb 22 '23 at 20:23
See if the answer to this: https://stackoverflow.com/questions/75521018/json-input-to-sql-table/75523240#75523240 gets you close. — Patrick Hurst, Feb 22 '23 at 21:01

score 1 · Accepted Answer · answered Feb 22 '23 at 21:38

first you can to Get Data MainTbl with openjson and Cte

second you Get DataSomeFieldTbl with openjson and Cte and use json

;with _list  as(
SELECT  
        a.uniqueId
            ,_id
        ,companyName
        ,yearFounded
   
  
        ,stateFounded
   

    FROM
    OPENJSON(@json)
    WITH
    (
            uniqueId varchar(100) N'$.uniqueId'
            ,_id varchar(100) N'$._id' 
        ,companyName varchar(100) N'$.companyName'
        ,yearFounded varchar(100) N'$.yearFounded'
        ,stateFounded varchar(100) N'$.stateFounded'

        
) AS a 

)

insert into MainTbl
(
         id  
        ,uniqueId 
        ,companyName 
         ,yearFounded 
         ,stateFounded 
 )
select 
         _id  
        ,uniqueId 
        ,companyName 
         ,yearFounded 
         ,stateFounded                                         
from _list  



;with _list  as(
SELECT  
            primaryCode
           ,secondaryCode
           ,_id
           ,tertiaryCode
           ,description

    FROM
    OPENJSON(@json)
    WITH
    (
         _id varchar(100) N'$._id' 
        ,someField NVARCHAR(MAX) AS JSON
) AS a
CROSS APPLY
OPENJSON(a.someField)
WITH
(
    primaryCode varchar(100) N'$.primaryCode'
    ,secondaryCode varchar(100) N'$.secondaryCode'
    ,tertiaryCode varchar(100) N'$.tertiaryCode'
    ,description varchar(100) N'$.description'

) AS b

)

insert into SomeFieldTbl
(
         Id_FK  
        ,primaryCode 
        ,secondaryCode 
         ,tertiaryCode 
         ,description 
 )
select  
         _id  
        ,primaryCode 
        ,secondaryCode 
         ,tertiaryCode 
         ,description                                          
from _list

The code below contains the entire code, including creating a table and inserting data to two tables


drop table if exists SomeFieldTbl
drop table if exists MainTbl


create table SomeFieldTbl(
Id_FK varchar(100),
primaryCode varchar(100)
,secondaryCode varchar(100),tertiaryCode varchar(100) 
 ,description varchar(100)
 )

create table MainTbl(
id varchar(100)
,uniqueId varchar(100),companyName varchar(100) 
 ,yearFounded varchar(100)
 ,stateFounded varchar(100))


declare @json varchar(max)='[
  {
    "_id": "12345",
    "uniqueId": null,
    "companyName": "ABC Corp",
    "yearFounded": 1958,
    "stateFounded": "Delaware",
    "someField": [
      {
        "primaryCode": "14",
        "secondaryCode": "32",
        "tertiaryCode": "00",
        "description": "Moving Walks"
      },
      {
        "primaryCode": "14",
        "secondaryCode": "40",
        "tertiaryCode": "00",
        "description": "Lifts"
      },
      {
        "primaryCode": "14",
        "secondaryCode": "00",
        "tertiaryCode": "00",
        "description": "Conveying Equipment"
      }
    ],
    "SomeRegionProperty": [
      {
        "region": "Other"
      },
      {
        "region": "MD - Eastern Shore"
      },
      {
        "region": "MD - Southern"
      },
      {
        "region": "MD - Central (incl. Baltimore)"
      }
    ],
    "Markets": [
      {
        "market": "Pharmaceutical & Laboratory"
      },
      {
        "market": "Retail"
      }
    ],
    "SomeEmptyProperty": [],
    "AndYetAnother": [
      {
        "unionName": "Name",
        "unionNumber": "Value 1234"
      }
    ]
  },
  {
    "_id": "949690",
    "companyName": "XYZ Co",
    "yearFounded": 2015,
    "stateFounded": "New York",
    "someField": [
      {
        "primaryCode": "15",
        "secondaryCode": "62",
        "tertiaryCode": "032",
        "description": "test"
      }
    ],
    "SomeRegionProperty": [
      {
        "region": "Other"
      },
      {
        "region": "MD - Eastern Shore"
      },
      {
        "region": "MD - Southern"
      },
      {
        "region": "MD - Central (incl. Baltimore)"
      }
    ],
    "Markets": [
      {
        "market": "Pharmaceutical & Laboratory"
      },
      {
        "market": "Retail"
      }
    ],
    "SomeEmptyProperty": [],
    "AndYetAnother": [
      {
        "unionName": "Name",
        "unionNumber": "Value 1234"
      }
    ]
  }
]

'

;with _list  as(
SELECT  
        a.uniqueId
            ,_id
        ,companyName
        ,yearFounded
   
  
        ,stateFounded
   

    FROM
    OPENJSON(@json)
    WITH
    (
            uniqueId varchar(100) N'$.uniqueId'
            ,_id varchar(100) N'$._id' 
        ,companyName varchar(100) N'$.companyName'
        ,yearFounded varchar(100) N'$.yearFounded'
        ,stateFounded varchar(100) N'$.stateFounded'

        
) AS a 

)

insert into MainTbl
(
         id  
        ,uniqueId 
        ,companyName 
         ,yearFounded 
         ,stateFounded 
 )
select 
         _id  
        ,uniqueId 
        ,companyName 
         ,yearFounded 
         ,stateFounded                                         
from _list  



;with _list  as(
SELECT  
            primaryCode
           ,secondaryCode
           ,_id
           ,tertiaryCode
           ,description

    FROM
    OPENJSON(@json)
    WITH
    (
         _id varchar(100) N'$._id' 
        ,someField NVARCHAR(MAX) AS JSON
) AS a
CROSS APPLY
OPENJSON(a.someField)
WITH
(
    primaryCode varchar(100) N'$.primaryCode'
    ,secondaryCode varchar(100) N'$.secondaryCode'
    ,tertiaryCode varchar(100) N'$.tertiaryCode'
    ,description varchar(100) N'$.description'

) AS b

)

insert into SomeFieldTbl
(
         Id_FK  
        ,primaryCode 
        ,secondaryCode 
         ,tertiaryCode 
         ,description 
 )
select  
         _id  
        ,primaryCode 
        ,secondaryCode 
         ,tertiaryCode 
         ,description                                          
from _list

Thank you for the answer. This is exactly the approach that I took. I have many (8-12) of those nested tables (json sub-objects) and each can potentially grow to have a very large data sets. I am planning to hit the stored procedure doing this from the Azure data factory, and noticed it takes a bit of time to complete the request. I was asking a question, hoping there may be some more creative method. — Dmitriy Ryabin, Feb 23 '23 at 13:59

OPENJSON - parsing nested JSON preserving relations

1 Answers1