Parsing nested json in sql server
Hello Everybody,
I have the following json where there can be multiple vehicles and each vehicle can contain multiple trips. I need to insert vehicles in to the TVHC table and for each vehicle insert trips into TTRP table.
Code:
[
{
"VHC_VTY_CDE": "CAR",
"VHC_NAT_CDE": "USA",
"VHC_MAN_YEAR": "2020",
"VHC_PLT_NBR": "ABC123",
"VHC_PLT_NAT_CDE": "USA",
"VHC_STA_ID": "1",
"VHC_STA_CDE": "P",
"VHC_COL_CDE":"",
"Trips":[
{
"TRP_DTE":"2024-01-30",
"TRP_TIME":"12:00:00.0000000",
"TRP_BRD_CDE":"061",
"TRP_AIR_CDE":"QTR",
"TRP_SHP_CDE":"S12",
"TRP_FGT_NBR":"CargoXYZ",
"TRP_SHP_NBR":"S12"
},
{
"TRP_DTE":"2024-01-30",
"TRP_TIME":"12:00:00.0000000",
"TRP_BRD_CDE":"061",
"TRP_AIR_CDE":"QTR",
"TRP_SHP_CDE":"S12",
"TRP_FGT_NBR":"CargoXYZ",
"TRP_SHP_NBR":"S12"
}
]
}
]
I am using the following procedure to insert the data but getting error that :
Msg 102, Level 15, State 1, Procedure Usama_Test, Line 81 [Batch Start Line 0]
Incorrect syntax near ')'.
Code:
CREATE OR ALTER PROCEDURE [dbo].[Usama_Test]
@vehicles_trips_json NVARCHAR(MAX),
@TRV_ID AS BIGINT
AS
--BEGIN TRANSACTION
INSERT INTO TVHC
(
VHC_TRV_ID,
VHC_VTY_CDE,
VHC_NAT_CDE,
VHC_MAN_YEAR,
VHC_PLT_NBR,
VHC_PLT_NAT_CDE,
VHC_STY_ID,
VHC_STA_CDE,
VHC_COL_CDE
)
SELECT
@TRV_ID,
VHC_VTY_CDE,
VHC_NAT_CDE,
VHC_MAN_YEAR,
VHC_PLT_NBR,
VHC_PLT_NAT_CDE,
VHC_STY_ID,
VHC_STA_CDE,
VHC_COL_CDE
FROM
OPENJSON(@vehicles_trips_json)
WITH (
[VHC_VTY_CDE] char(3) N'$.VHC_VTY_CDE',
[VHC_NAT_CDE] char(3) N'$.VHC_NAT_CDE',
[VHC_MAN_YEAR] char(4) N'$.VHC_MAN_YEAR',
[VHC_PLT_NBR] varchar(20) N'$.VHC_PLT_NBR',
[VHC_PLT_NAT_CDE] char(3) N'$.VHC_PLT_NAT_CDE',
[VHC_STY_ID] int N'$.VHC_STY_ID',
[VHC_STA_CDE] int N'$.VHC_STA_CDE',
[VHC_COL_CDE] char(10) N'$.VHC_COL_CDE',
[Trips] nvarchar(max) N'$.Trips'
) AS ROOT_JSON
OUTER APPLY
(
INSERT INTO TTRP
(
TRP_DTE,
TRP_TIME,
TRP_VHC_ID,
TRP_BRD_CDE,
TRP_AIR_CDE,
TRP_SHP_CDE,
TRP_FGT_NBR,
TRP_SHP_NBR
)
SELECT
TRP_DTE,
TRP_TIME,
@@IDENTITY,
TRP_BRD_CDE,
TRP_AIR_CDE,
TRP_SHP_CDE,
TRP_FGT_NBR,
TRP_SHP_NBR
FROM
OPENJSON(ROOT_JSON.Trips)
WITH (
[TRP_DTE] date N'$.TRP_DTE',
[TRP_TIME] time(7) N'$.TRP_TIME',
[TRP_BRD_CDE] char(3) N'$.TRP_BRD_CDE',
[TRP_AIR_CDE] char(3) N'$.TRP_AIR_CDE',
[TRP_SHP_CDE] char(3) N'$.TRP_SHP_CDE',
[TRP_FGT_NBR] varchar(20) N'$.TRP_FGT_NBR',
[TRP_SHP_NBR] varchar(20) N'$.TRP_SHP_NBR'
)
)
--COMMIT TRANSACTION
Any ideas??
Re: Parsing nested json in sql server
You say you need to do this "I need to insert vehicles into the TVHC table and for each vehicle insert trips into TTRV table." but the Insert Into on the outer apply is inserting into TTRP and not TTRV. Which has the typo?
Re: Parsing nested json in sql server
Quote:
Originally Posted by
jdelano
You say you need to do this "I need to insert vehicles into the TVHC table and for each vehicle insert trips into TTRV table." but the Insert Into on the outer apply is inserting into TTRP and not TTRV. Which has the typo?
Sorry it was a type. I corrected.
Re: Parsing nested json in sql server
Is there a closing semicolon missing after the last ")"?
Re: Parsing nested json in sql server
1. No, you cannot OUTER APPLY an INSERT as it has to be a derived table i.e. some kind of SELECT.
2. You need AS JSON suffix on the [Trips] nvarchar(max) N'$.Trips' in the WITH clause to extract the non-scalar otherwise it will return NULL. This is like JSON_VALUE vs JSON_QUERY -- you use the latter for objects or arrays and the former for scalar types only.
3. If you are going to insert into mulitple tables (multiple INSERT statements) you'll need a TRANSACTION.
4. You don't need the JSON Path when column name is the same as the field in the source JSON i.e. this [VHC_VTY_CDE] char(3) N'$.VHC_VTY_CDE' is the same as [VHC_VTY_CDE] char(3) because the column path is implied by the column name when missing.
5. For the second insert into TTRP you'll need the synthetic PKs of just inserted rows in TVHC which is not present in the JSON
6. Field VHC_STA_CDE cannot be an INT with values like 'p' in the JSON.
7. Btw, there is no field VHC_STY_ID in the JSON but VHC_STA_ID
8. Semicolons are optional in T-SQL (and in JavaScript)
Try this sample code
Code:
DECLARE @JSON VARCHAR(MAX) = '[
{
"VHC_VTY_CDE": "CAR1",
"VHC_NAT_CDE": "USA",
"VHC_MAN_YEAR": "2020",
"VHC_PLT_NBR": "ABC123",
"VHC_PLT_NAT_CDE": "USA",
"VHC_STA_ID": "1",
"VHC_STA_CDE": "P",
"VHC_COL_CDE":"",
"Trips":[
{
"TRP_DTE":"2024-01-30",
"TRP_TIME":"12:00:00.0000000",
"TRP_BRD_CDE":"061",
"TRP_AIR_CDE":"QTR",
"TRP_SHP_CDE":"S12",
"TRP_FGT_NBR":"CargoXYZ",
"TRP_SHP_NBR":"S12"
},
{
"TRP_DTE":"2024-01-30",
"TRP_TIME":"12:00:00.0000000",
"TRP_BRD_CDE":"062",
"TRP_AIR_CDE":"QTR",
"TRP_SHP_CDE":"S12",
"TRP_FGT_NBR":"CargoXYZ",
"TRP_SHP_NBR":"S12"
}
]
},
{
"VHC_VTY_CDE": "CAR2",
"VHC_NAT_CDE": "USA2",
"VHC_MAN_YEAR": "2020",
"VHC_PLT_NBR": "ABC123",
"VHC_PLT_NAT_CDE": "USA",
"VHC_STA_ID": "1",
"VHC_STA_CDE": "P",
"VHC_COL_CDE":"",
"Trips":[
{
"TRP_DTE":"2024-01-30",
"TRP_TIME":"12:00:00.0000000",
"TRP_BRD_CDE":"063",
"TRP_AIR_CDE":"QTR",
"TRP_SHP_CDE":"S12",
"TRP_FGT_NBR":"CargoXYZ",
"TRP_SHP_NBR":"S12"
},
{
"TRP_DTE":"2024-01-30",
"TRP_TIME":"12:00:00.0000000",
"TRP_BRD_CDE":"064",
"TRP_AIR_CDE":"QTR",
"TRP_SHP_CDE":"S12",
"TRP_FGT_NBR":"CargoXYZ",
"TRP_SHP_NBR":"S12"
}
]
}
]'
DROP TABLE IF EXISTS #TmpTVHC, #TmpTTRP
SELECT DENSE_RANK() OVER (ORDER BY VHC_VTY_CDE, VHC_NAT_CDE, VHC_MAN_YEAR, VHC_PLT_NBR
, VHC_PLT_NAT_CDE, VHC_STA_ID, VHC_STA_CDE, VHC_COL_CDE) AS VHC_TRV_ID
, p.*
INTO #TmpTVHC
FROM OPENJSON(@JSON)
WITH (
VHC_VTY_CDE NVARCHAR(MAX)
, VHC_NAT_CDE NVARCHAR(MAX)
, VHC_MAN_YEAR NVARCHAR(MAX)
, VHC_PLT_NBR NVARCHAR(MAX)
, VHC_PLT_NAT_CDE NVARCHAR(MAX)
, VHC_STA_ID NVARCHAR(MAX)
, VHC_STA_CDE NVARCHAR(MAX)
, VHC_COL_CDE NVARCHAR(MAX)
) p
SELECT DENSE_RANK() OVER (ORDER BY VHC_VTY_CDE, VHC_NAT_CDE, VHC_MAN_YEAR, VHC_PLT_NBR
, VHC_PLT_NAT_CDE, VHC_STA_ID, VHC_STA_CDE, VHC_COL_CDE) AS VHC_TRV_ID
, s.*
INTO #TmpTTRP
FROM OPENJSON(@JSON)
WITH (
VHC_VTY_CDE NVARCHAR(MAX)
, VHC_NAT_CDE NVARCHAR(MAX)
, VHC_MAN_YEAR NVARCHAR(MAX)
, VHC_PLT_NBR NVARCHAR(MAX)
, VHC_PLT_NAT_CDE NVARCHAR(MAX)
, VHC_STA_ID NVARCHAR(MAX)
, VHC_STA_CDE NVARCHAR(MAX)
, VHC_COL_CDE NVARCHAR(MAX)
, Trips NVARCHAR(MAX) AS JSON
) p
OUTER APPLY (
SELECT *
FROM OPENJSON(p.Trips)
WITH (
TRP_DTE NVARCHAR(MAX)
, TRP_TIME NVARCHAR(MAX)
, TRP_BRD_CDE NVARCHAR(MAX)
, TRP_AIR_CDE NVARCHAR(MAX)
, TRP_SHP_CDE NVARCHAR(MAX)
, TRP_FGT_NBR NVARCHAR(MAX)
, TRP_SHP_NBR NVARCHAR(MAX)
)
) s
SELECT * FROM #TmpTVHC
SELECT * FROM #TmpTTRP
BEGIN TRAN
-- populate TVHC from #TmpTVHC
-- populate TTRP from #TmpTTRP
--ROLLBACK
COMMIT
You'll have to figure out how to populate base tables from the temp tables but so far IMO this is fine as a working example of handling JSON.
cheers,
</wqw>
Re: Parsing nested json in sql server
Quote:
Originally Posted by
wqweto
1. No, you cannot OUTER APPLY an INSERT as it has to be a derived table i.e. some kind of SELECT.
2. You need AS JSON suffix on the
[Trips] nvarchar(max) N'$.Trips' in the WITH clause to extract the non-scalar otherwise it will return NULL. This is like JSON_VALUE vs JSON_QUERY -- you use the latter for objects or arrays and the former for scalar types only.
3. If you are going to insert into mulitple tables (multiple INSERT statements) you'll need a TRANSACTION.
4. You don't need the JSON Path when column name is the same as the field in the source JSON i.e. this
[VHC_VTY_CDE] char(3) N'$.VHC_VTY_CDE' is the same as
[VHC_VTY_CDE] char(3) because the column path is implied by the column name when missing.
5. For the second insert into TTRP you'll need the synthetic PKs of just inserted rows in TVHC which is not present in the JSON
6. Field VHC_STA_CDE cannot be an INT with values like 'p' in the JSON.
7. Btw, there is no field VHC_ST
Y_ID in the JSON but VHC_ST
A_ID
8. Semicolons are optional in T-SQL (and in JavaScript)
Try this sample code
Code:
DECLARE @JSON VARCHAR(MAX) = '[
{
"VHC_VTY_CDE": "CAR1",
"VHC_NAT_CDE": "USA",
"VHC_MAN_YEAR": "2020",
"VHC_PLT_NBR": "ABC123",
"VHC_PLT_NAT_CDE": "USA",
"VHC_STA_ID": "1",
"VHC_STA_CDE": "P",
"VHC_COL_CDE":"",
"Trips":[
{
"TRP_DTE":"2024-01-30",
"TRP_TIME":"12:00:00.0000000",
"TRP_BRD_CDE":"061",
"TRP_AIR_CDE":"QTR",
"TRP_SHP_CDE":"S12",
"TRP_FGT_NBR":"CargoXYZ",
"TRP_SHP_NBR":"S12"
},
{
"TRP_DTE":"2024-01-30",
"TRP_TIME":"12:00:00.0000000",
"TRP_BRD_CDE":"062",
"TRP_AIR_CDE":"QTR",
"TRP_SHP_CDE":"S12",
"TRP_FGT_NBR":"CargoXYZ",
"TRP_SHP_NBR":"S12"
}
]
},
{
"VHC_VTY_CDE": "CAR2",
"VHC_NAT_CDE": "USA2",
"VHC_MAN_YEAR": "2020",
"VHC_PLT_NBR": "ABC123",
"VHC_PLT_NAT_CDE": "USA",
"VHC_STA_ID": "1",
"VHC_STA_CDE": "P",
"VHC_COL_CDE":"",
"Trips":[
{
"TRP_DTE":"2024-01-30",
"TRP_TIME":"12:00:00.0000000",
"TRP_BRD_CDE":"063",
"TRP_AIR_CDE":"QTR",
"TRP_SHP_CDE":"S12",
"TRP_FGT_NBR":"CargoXYZ",
"TRP_SHP_NBR":"S12"
},
{
"TRP_DTE":"2024-01-30",
"TRP_TIME":"12:00:00.0000000",
"TRP_BRD_CDE":"064",
"TRP_AIR_CDE":"QTR",
"TRP_SHP_CDE":"S12",
"TRP_FGT_NBR":"CargoXYZ",
"TRP_SHP_NBR":"S12"
}
]
}
]'
DROP TABLE IF EXISTS #TmpTVHC, #TmpTTRP
SELECT DENSE_RANK() OVER (ORDER BY VHC_VTY_CDE, VHC_NAT_CDE, VHC_MAN_YEAR, VHC_PLT_NBR
, VHC_PLT_NAT_CDE, VHC_STA_ID, VHC_STA_CDE, VHC_COL_CDE) AS VHC_TRV_ID
, p.*
INTO #TmpTVHC
FROM OPENJSON(@JSON)
WITH (
VHC_VTY_CDE NVARCHAR(MAX)
, VHC_NAT_CDE NVARCHAR(MAX)
, VHC_MAN_YEAR NVARCHAR(MAX)
, VHC_PLT_NBR NVARCHAR(MAX)
, VHC_PLT_NAT_CDE NVARCHAR(MAX)
, VHC_STA_ID NVARCHAR(MAX)
, VHC_STA_CDE NVARCHAR(MAX)
, VHC_COL_CDE NVARCHAR(MAX)
) p
SELECT DENSE_RANK() OVER (ORDER BY VHC_VTY_CDE, VHC_NAT_CDE, VHC_MAN_YEAR, VHC_PLT_NBR
, VHC_PLT_NAT_CDE, VHC_STA_ID, VHC_STA_CDE, VHC_COL_CDE) AS VHC_TRV_ID
, s.*
INTO #TmpTTRP
FROM OPENJSON(@JSON)
WITH (
VHC_VTY_CDE NVARCHAR(MAX)
, VHC_NAT_CDE NVARCHAR(MAX)
, VHC_MAN_YEAR NVARCHAR(MAX)
, VHC_PLT_NBR NVARCHAR(MAX)
, VHC_PLT_NAT_CDE NVARCHAR(MAX)
, VHC_STA_ID NVARCHAR(MAX)
, VHC_STA_CDE NVARCHAR(MAX)
, VHC_COL_CDE NVARCHAR(MAX)
, Trips NVARCHAR(MAX) AS JSON
) p
OUTER APPLY (
SELECT *
FROM OPENJSON(p.Trips)
WITH (
TRP_DTE NVARCHAR(MAX)
, TRP_TIME NVARCHAR(MAX)
, TRP_BRD_CDE NVARCHAR(MAX)
, TRP_AIR_CDE NVARCHAR(MAX)
, TRP_SHP_CDE NVARCHAR(MAX)
, TRP_FGT_NBR NVARCHAR(MAX)
, TRP_SHP_NBR NVARCHAR(MAX)
)
) s
SELECT * FROM #TmpTVHC
SELECT * FROM #TmpTTRP
BEGIN TRAN
-- populate TVHC from #TmpTVHC
-- populate TTRP from #TmpTTRP
--ROLLBACK
COMMIT
You'll have to figure out how to populate base tables from the temp tables but so far IMO this is fine as a working example of handling JSON.
cheers,
</wqw>
Thanks for your time and effort, I'll put this in my actual procedure with INSERT into the table and test it.
Re: Parsing nested json in sql server
Is there a missing closing semicolon after the last ")" in your SQL Server code, and have you verified its presence?
