title: Import JSON File into SQL Server Table with nested Arrays
body: <p>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?</p>
  
  <pre><code>Declare @GeoJSON varchar(max)
  
  SELECT @GeoJSON = BulkColumn
  FROM OPENROWSET (BULK 'C:\Temp\Census_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 
  </code></pre>
  
  <p>The output is as follows:</p>
  
  <pre><code>+----------+---------+-------+-----------------+-----------------+-------------+--------------------+--------------------+------------+-----------+----------+
  | 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     |
  +----------+---------+-------+-----------------+-----------------+-------------+--------------------+--------------------+------------+-----------+----------+
  </code></pre>
  
  <p>The GeoJSON file structure is as follows:</p>
  
  <pre><code>{
     "type":"FeatureCollection",
     "features":[
        {
           "type":"Feature",
           "geometry":{
              "type":"Polygon",
              "coordinates":[
              ]
           },
           "properties":{
           }
        }
     ]
  }
  </code></pre>
  
  <p>The GEOJSON file is available <a href="https://opendata.arcgis.com/datasets/a6f76663621548e1a039798784b64f10_0.geojson" rel="nofollow noreferrer">here</a>.</p>