OPENJSON - parsing nested JSON preserving relations

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.

Solution

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