Parsing nested json in sql server

[usamaalam]

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.

[
  {
    "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 ')'.

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