Help importing a JSON file using OPENJSON

Transact-SQL
1

Hi

I'm trying to import the data part of this json into separate rows (i've provided sample data with two). I can get the size part of the json to show using this query below: but when I try with data it comes up as "null" and a single row. I've not done this before so any assistance would be much appreciated.

Declare @JSON varchar(max)
SELECT @JSON=BulkColumn
FROM OPENROWSET (BULK 'C:\Json\datacut.json', SINGLE_CLOB) import
If (ISJSON(@JSON)=1)

SELECT *
FROM OPENJSON (@json) WITH (
columns int '.size.columns', rows int '.size.rows',
YOA INT '$.data.YOA'
)

ELSE
Print 'Error in JSON format'

{
"size":{

},
"columns":[

],
"data":[
{
"Dev Qtr":"1",
"YOA":"1901",
"Risk Code":"XBox",
"Gross Premium":"13,015,435",
"Acquisition Cost":"-4,157,329",
"Net Premium":"33,858,106",
"Claims Paid":"0",
"Claims Incurred":"-19,534",
"Claims Outstanding":"-79,534",
"Gross Paid LR":"0.0%",
"Net Paid LR":"0.0%",
"Gross Incurred LR":"0.0%",
"Net Incurred LR":"0.0%"
},
{
"Dev Qtr":"1",
"YOA":"1980",
"Risk Code":"PIL",
"Gross Premium":"15,841,756",
"Acquisition Cost":"-18,590,831",
"Net Premium":"15,250,925",
"Claims Paid":"-3,626",
"Claims Incurred":"-12,676",
"Claims Outstanding":"-129,050",
"Gross Paid LR":"0.0%",
"Net Paid LR":"0.0%",
"Gross Incurred LR":"0.1%",
"Net Incurred LR":"0.1%"
}
]
}

2

hope this link helps ...

https://www.sqlshack.com/import-json-data-into-sql-server/

3

Thats what I used to get as far as I did, I dont understand how I can pull in the column row number but nothing from data, YOA is null etc using my code

4

The reason is the data section in the json is itself JSON data

select *
  from (
		  select data, columns, size
		  from @sample c 
		 CROSS APPLY OPENJSON(c.json_column) 
		 with (
				data  NVARCHAR(MAX)  AS JSON,
				columns NVARCHAR(MAX)  AS JSON,
				size VARCHAR(200) '$.size'
			  )

	   ) a
CROSS APPLY OPENJSON(data) 
 WITH (
		YOA VARCHAR(200)   '$.YOA'
	  )
5

Also take a look on how to deconstruct json data

select *
  from @sample c 

 select YOA
  from @sample c 
 CROSS APPLY OPENJSON(c.json_column, 'lax $.data') 
 WITH (   
		 YOA   VARCHAR(200)   '$.YOA'
	  )
 
 select YOA
  from @sample c 
 CROSS APPLY OPENJSON(c.json_column, '$.data')  
 WITH (   
              YOA   VARCHAR(200)   '$.YOA'
		)
6

Thank you so much!! much appreciated!!