SQLTeam.com | Weblogs | Forums

Help importing a JSON file using OPENJSON

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%"
}
]
}

hope this link helps ...

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

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

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'
	  )

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'
		)

Thank you so much!! much appreciated!!