Separate JSON Arrays in PostgreSQL

Question

I have uploaded a JSON file into PostgreSQL 10, but my output is the following:

{
  "type":"name",
  "properties":{
    "name":"urn:ogc:def:crs:OGC:1.3:CRS84"
  }
}

[
  {
    "type":"Feature",
    "geometry":{
      "type":"Polygon",
      "coordinates":[
        [
          [
            -69.63156338410452,
            18.423064855125194
          ],
          [
            -69.63158484940756,
            18.42306049063717
          ],
          [
            -69.6316055531327,
            18.42316231511968
          ],
          [
            -69.63158408782962,
            18.4231666796077
          ],
          [
            -69.63156338410452,
            18.423064855125194
          ]
        ]
      ]
    },
    "properties":{
      "id":579,
      "top":2087416.483723811,
      "left":-7751401.453397826,
      "right":-7751399.014997826,
      "bottom":2087404.291723811
    }
  },
  {
    "type":"Feature",
    "geometry":{
      "type":"Polygon",
      "coordinates":[
        [
          [
            -69.63158408782962,
            18.4231666796077
          ],
          [
            -69.6316055531327,
            18.42316231511968
          ],
          [
            -69.6316262568703,
            18.42326413966371
          ],
          [
            -69.63160479156727,
            18.423268504151732
          ],
          [
            -69.63158408782962,
            18.4231666796077
          ]
        ]
      ]
    },
    "properties":{
      "id":580,
      "top":2087404.291723811,
      "left":-7751401.453397826,
      "right":-7751399.014997826,
      "bottom":2087392.099723811
    }
  },
  {
    "type":"Feature",
    "geometry":{
      "type":"Polygon",
      "coordinates":[
        [
          [
            -69.63160479156727,
            18.423268504151732
          ],
          [
            -69.6316262568703,
            18.42326413966371
          ],
          [
            -69.63164696062047,
            18.423365964269255
          ],
          [
            -69.6316254953174,
            18.42337032875728
          ],
          [
            -69.63160479156727,
            18.423268504151732
          ]
        ]
      ]
    },
    "properties":{
      "id":581,
      "top":2087392.099723811,
      "left":-7751401.453397826,
      "right":-7751399.014997826,
      "bottom":2087379.907723811
    }
  },
  {
    "type":"Feature",
    "geometry":{
      "type":"Polygon",
      "coordinates":[
        [
          [
            -69.6316254953174,
            18.423370328757276
          ],
          [
            -69.63164696062047,
            18.423365964269255
          ],
          [
            -69.6316676643831,
            18.423467788936325
          ],
          [
            -69.63164619908007,
            18.423472153424346
          ],
          [
            -69.6316254953174,
            18.423370328757276
          ]
        ]
      ]
    },
    "properties":{
      "id":582,
      "top":2087379.9077238112,
      "left":-7751401.453397826,
      "right":-7751399.014997826,
      "bottom":2087367.7157238112
    }
  },
  {
    "type":"Feature",
    "geometry":{
      "type":"Polygon",
      "coordinates":[
        [
          [
            -69.63164619908007,
            18.423472153424346
          ],
          [
            -69.6316676643831,
            18.423467788936325
          ],
          [
            -69.63168836815828,
            18.423569613664906
          ],
          [
            -69.63166690285523,
            18.42357397815293
          ],
          [
            -69.63164619908007,
            18.423472153424346
          ]
        ]
      ]
    },
    "properties":{
      "id":583,
      "top":2087367.7157238112,
      "left":-7751401.453397826,
      "right":-7751399.014997826,
      "bottom":2087355.5237238111
    }
  },
  {
    "type":"Feature",
    "geometry":{
      "type":"Polygon",
      "coordinates":[
        [
          [
            -69.63166690285523,
            18.42357397815293
          ],
          [
            -69.63168836815828,
            18.423569613664906
          ],
          [
            -69.63170907194598,
            18.423671438455013
          ],
          [
            -69.63168760664291,
            18.423675802943034
          ],
          [
            -69.63166690285523,
            18.42357397815293
          ]
        ]
      ]
    },
    "properties":{
      "id":584,
      "top":2087355.5237238111,
      "left":-7751401.453397826,
      "right":-7751399.014997826,
      "bottom":2087343.331723811
    }
  },
  {
    "type":"Feature",
    "geometry":{
      "type":"Polygon",
      "coordinates":[
        [
          [
            -69.63158484940756,
            18.42306049063717
          ],
          [
            -69.63160631471062,
            18.423056126149138
          ],
          [
            -69.63162701843578,
            18.423157950631648
          ],
          [
            -69.6316055531327,
            18.42316231511968
          ],
          [
            -69.63158484940756,
            18.42306049063717
          ]
        ]
      ]
    },
    "properties":{
      "id":596,
      "top":2087416.483723811,
      "left":-7751399.014997827,
      "right":-7751396.576597827,
      "bottom":2087404.291723811
    }
  },
  {
    "type":"Feature",
    "geometry":{
      "type":"Polygon",
      "coordinates":[
        [
          [
            -69.6316055531327,
            18.42316231511968
          ],
          [
            -69.63162701843578,
            18.423157950631648
          ],
          [
            -69.63164772217337,
            18.423259775175687
          ],
          [
            -69.6316262568703,
            18.42326413966371
          ],
          [
            -69.6316055531327,
            18.42316231511968
          ]
        ]
      ]
    },
    "properties":{
      "id":597,
      "top":2087404.291723811,
      "left":-7751399.014997827,
      "right":-7751396.576597827,
      "bottom":2087392.099723811
    }
  },
  {
    "type":"Feature",
    "geometry":{
      "type":"Polygon",
      "coordinates":[
        [
          [
            -69.6316262568703,
            18.42326413966371
          ],
          [
            -69.63164772217337,
            18.423259775175687
          ],
          [
            -69.63166842592354,
            18.423361599781227
          ],
          [
            -69.63164696062047,
            18.423365964269255
          ],
          [
            -69.6316262568703,
            18.42326413966371
          ]
        ]
      ]
    },
    "properties":{
      "id":598,
      "top":2087392.099723811,
      "left":-7751399.014997827,
      "right":-7751396.576597827,
      "bottom":2087379.907723811
    }
  },
  {
    "type":"Feature",
    "geometry":{
      "type":"Polygon",
      "coordinates":[
        [
          [
            -69.63164696062047,
            18.423365964269255
          ],
          [
            -69.63166842592354,
            18.423361599781227
          ],
          [
            -69.6316891296862,
            18.423463424448297
          ],
          [
            -69.6316676643831,
            18.423467788936325
          ],
          [
            -69.63164696062047,
            18.423365964269255
          ]
        ]
      ]
    },
    "properties":{
      "id":599,
      "top":2087379.9077238112,
      "left":-7751399.014997827,
      "right":-7751396.576597827,
      "bottom":2087367.7157238112
    }
  },
  {
    "type":"Feature",
    "geometry":{
      "type":"Polygon",
      "coordinates":[
        [
          [
            -69.6316676643831,
            18.423467788936325
          ],
          [
            -69.6316891296862,
            18.423463424448297
          ],
          [
            -69.63170983346134,
            18.423565249176885
          ],
          [
            -69.63168836815828,
            18.423569613664906
          ],
          [
            -69.6316676643831,
            18.423467788936325
          ]
        ]
      ]
    },
    "properties":{
      "id":600,
      "top":2087367.7157238112,
      "left":-7751399.014997827,
      "right":-7751396.576597827,
      "bottom":2087355.5237238111
    }
  },
  {
    "type":"Feature",
    "geometry":{
      "type":"Polygon",
      "coordinates":[
        [
          [
            -69.63168836815828,
            18.423569613664906
          ],
          [
            -69.63170983346134,
            18.423565249176885
          ],
          [
            -69.63173053724903,
            18.423667073966993
          ],
          [
            -69.63170907194598,
            18.423671438455013
          ],
          [
            -69.63168836815828,
            18.423569613664906
          ]
        ]
      ]
    },
    "properties":{
      "id":601,
      "top":2087355.5237238111,
      "left":-7751399.014997827,
      "right":-7751396.576597827,
      "bottom":2087343.331723811
    }
  },
  {
    "type":"Feature",
    "geometry":{
      "type":"Polygon",
      "coordinates":[
        [
          [
            -69.63160631471062,
            18.423056126149138
          ],
          [
            -69.6316277800137,
            18.42305176166112
          ],
          [
            -69.63164848373884,
            18.423153586143624
          ],
          [
            -69.63162701843578,
            18.423157950631648
          ],
          [
            -69.63160631471062,
            18.423056126149138
          ]
        ]
      ]
    },
    "properties":{
      "id":613,
      "top":2087416.483723811,
      "left":-7751396.576597827,
      "right":-7751394.138197826,
      "bottom":2087404.291723811
    }
  }
]
</cod

Answer 1

There are two bits of json in the string above. First,

"{"type": "name", "properties": {"name": "urn:ogc:def:crs:OGC:1.3:CRS84"}}"

And then an array which looks much better with proper indentation indented (results here):

[
  {
    "type": "Feature",
    "geometry": {
      "type": "Polygon",
      "coordinates": [
        [
          [
            -69.63156338410452,
            18.423064855125194
          ],
          [
            -69.63158484940756,
            18.42306049063717
          ],
          [
            -69.6316055531327,
            18.42316231511968
          ],
          [
            -69.63158408782962,
            18.4231666796077
          ],
          [
            -69.63156338410452,
            18.423064855125194
          ]
        ]
      ]
    },
    "properties": {
      "id": 579,
      "top": 2087416.483723811,
      "left": -7751401.453397826,
      "right": -7751399.014997826,
      "bottom": 2087404.291723811
    }
  },

So, I'll assume all the objects have the same structure, similar as above. Let's put the json into a table for convenience...

CREATE TABLE foo ( d jsonb );
INSERT INTO foo VALUES ('the above json data')

The following should do what you want, including conversion to postgres geometric types:

SELECT
typ,
BOX( POINT((prop->>'left')::FLOAT,  (prop->>'top')::FLOAT),
     POINT((prop->>'right')::FLOAT, (prop->>'bottom')::FLOAT) ) bbox,
     REPLACE(REPLACE((geom->'coordinates')->>0,'[','('),']',')')::POLYGON
FROM (
SELECT 
    elem->>'type'         AS typ,
    elem->'properties'    AS prop,
    elem->'geometry'      AS geom
FROM (
SELECT jsonb_array_elements(d) elem FROM foo
) f1
)f2;

However, processing this kind of json in a SQL query is not flexible, so if you have several types of geometry (not just polygons) or if the json structure can vary, it would be better to process it in a more suitable language before using SQL.