Multilevel JSON file import from T-SQL

Question

I am trying to read the second level columns but just get NULL values, the first ones, datasetid and recordid are fine though.

DECLARE @vJSON VARCHAR(MAX);

SELECT @vJSON = BulkColumn
FROM OPENROWSET (BULK 'c:\adminsql\provincias-espanolas.json', Single_CLOB) AS import;

If (ISJSON(@vJSON)=1)
Print 'It is a valid JSON'
ELSE
Print 'Error in JSON format'

SELECT * FROM OPENJSON(@vJSON)
--SELECT datasetid, recordid,codigo,[geo_point_2d],[ccaa],[provincia],[cod_ccaa],[geo_shape] FROM OPENJSON(@vJSON)
with
(
[datasetid] varchar(50)
,[recordid] varchar(200)
--,[fields[0]].[codigo] varchar(2000)
,[codigo] int -- varchar(200)
,[geo_point_2d] varchar(max)
,[ccaa] varchar(500)
,[provincia] varchar(500)
,[cod_ccaa] varchar(50)
,[geo shape] varchar(max)

)

Answer 1

To get the value of codigo, probably you should write:

. . .
[codigo] int '$.fields.codigo', 
. . .

If you must distinguish "05" and "5", then use a text type instead of int.

Specify the path for other fields in the same manner. It is difficult to guess some of the paths; maybe you should show or attach the JSON file if you have issues.