Import JSON File into SQL Server Table with nested Arrays

I’m trying to import the Census Block GeoJSON file and unable to get “Coordinates” for each block along with it’s properties. I’m trying to get the ID, BlockGrp, Block.. and it’s associated coordinates. Below is my code, but I’m unable to get the coordinates since it’s in an array. Can anybody guide me on how to make it work?

Declare @GeoJSON varchar(max)

SELECT @GeoJSON = BulkColumn
FROM OPENROWSET (BULK 'C:TempCensus_Blocks__2010.geojson', SINGLE_CLOB) as j


SELECT *
FROM OPENJSON (@GeoJSON,'$.features')
WITH
    (
        OBJECTID INT N'$.properties.OBJECTID'
        , BLKGRP NVARCHAR(10) N'$.properties.BLKGRP'
        , BLOCK INT N'$.properties.BLOCK'
        , GEOID NVARCHAR(100) N'$.properties.GEOID'
        , GEOID10 NVARCHAR(100) N'$.properties.GEOID10'
        , ACRES nvarchar(100) N'$.properties.ACRES'
        , Shape_Length nvarchar(100) N'$.properties.Shape_length'
        , Shape_Area nvarchar(100) N'$.properties.Shape_Area'
        , SQMILES nvarchar(100) N'$.properties.SQMILES'
        , Longitude nvarchar(100) N'$.geometry.coordinates[0]'
        , Latitude nvarchar(100) N'$.geometry.coordinates[1]'
    ) a 

The output is as follows:

+----------+---------+-------+-----------------+-----------------+-------------+--------------------+--------------------+------------+-----------+----------+
| OBJECTID | BLKGRP  | BLOCK |      GEOID      |     GEOID10     |    ACRES    |    Shape_Length    |     Shape_Area     |  SQMILES   | Longitude | Latitude |
+----------+---------+-------+-----------------+-----------------+-------------+--------------------+--------------------+------------+-----------+----------+
|        1 | 0005011 |  1004 | 110010005011004 | 110010005011004 | 92.90825947 | 3646.7801257671467 | 375986.38657525991 | 0.14516916 | NULL      | NULL     |
|        2 | 0005011 |  1005 | 110010005011005 | 110010005011005 |  4.22602654 | 600.80242048281752 | 17102.122624542077 | 0.00660317 | NULL      | NULL     |
|        3 | 0005011 |  1006 | 110010005011006 | 110010005011006 |  3.37694114 | 567.78401560218686 | 13665.995959875707 | 0.00527647 | NULL      | NULL     |
|        4 | 0005011 |  1007 | 110010005011007 | 110010005011007 |   6.2465494 |  784.3194030589018 | 25278.888549948519 | 0.00976023 | NULL      | NULL     |
|        5 | 0005011 |  1008 | 110010005011008 | 110010005011008 |  0.45035641 | 233.98753402256077 | 1822.5277124594836 | 0.00070368 | NULL      | NULL     |
|        6 | 0005011 |  1009 | 110010005011009 | 110010005011009 |  2.54391236 | 523.98099364773702 | 10294.848087676977 | 0.00397486 | NULL      | NULL     |
|        7 | 0005011 |  1010 | 110010005011010 | 110010005011010 |  3.65630529 | 511.54127551683035 | 14796.542550295248 | 0.00571298 | NULL      | NULL     |
|        8 | 0005011 |  1011 | 110010005011011 | 110010005011011 |  5.64727404 | 689.75830443180621 | 22853.707228554606 | 0.00882387 | NULL      | NULL     |
|        9 | 0005011 |  1012 | 110010005011012 | 110010005011012 |  7.38896984 | 856.70248366785154 | 29902.100049688841 | 0.01154527 | NULL      | NULL     |
|       10 | 0005011 |  1013 | 110010005011013 | 110010005011013 |  2.45065536 | 590.21583640085453 | 9917.4503661506897 | 0.00382915 | NULL      | NULL     |
+----------+---------+-------+-----------------+-----------------+-------------+--------------------+--------------------+------------+-----------+----------+

The GeoJSON file structure is as follows:

{
   "type":"FeatureCollection",
   "features":[
      {
         "type":"Feature",
         "geometry":{
            "type":"Polygon",
            "coordinates":[
            ]
         },
         "properties":{
         }
      }
   ]
}

The GEOJSON file is available here.

Answer

I think you are on the right track, you should just perform a few additional steps:

• retrieve the content of coordinates property using the AS JSON syntax
• add two additional OPENJSON statements to shred your array to the [Longitude,Latitude] level with CROSS APPLY statements
• retrieve Longitude and Latitude values with JSON_VALUE in your SELECT statement.

This is a sample query that should extract what you need:

SELECT 
      a.OBJECTID    
    , a.BLKGRP      
    , a.BLOCK       
    , a.GEOID       
    , a.GEOID10     
    , a.ACRES       
    , a.Shape_Length
    , a.Shape_Area  
    , a.SQMILES     
    , JSON_VALUE(array2,'$[0]') as Longitude 
    , JSON_VALUE(array2,'$[1]') as Latitude  
FROM OPENJSON (@GeoJSON,'$.features')
WITH
    (
          OBJECTID     INT           N'$.properties.OBJECTID'
        , BLKGRP       NVARCHAR(10)  N'$.properties.BLKGRP'
        , BLOCK        INT           N'$.properties.BLOCK'
        , GEOID        NVARCHAR(100) N'$.properties.GEOID'
        , GEOID10      NVARCHAR(100) N'$.properties.GEOID10'
        , ACRES        nvarchar(100) N'$.properties.ACRES'
        , Shape_Length nvarchar(100) N'$.properties.Shape_length'
        , Shape_Area   nvarchar(100) N'$.properties.Shape_Area'
        , SQMILES      nvarchar(100) N'$.properties.SQMILES'
        , coordinates  NVARCHAR(MAX) N'$.geometry.coordinates' AS JSON 
    ) a 
CROSS APPLY OPENJSON(coordinates) WITH (array nvarchar(max) N'$' as json) b
CROSS APPLY OPENJSON(array)       WITH (array2 nvarchar(max) N'$' as json) c

Sample output:

You can see this code in action on a subset of your data in this fiddle.