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