CODEWITHSUNDEEP
sqljsonpostgresqlnumbers
dwool
dwool

Reputation: 29

Extract deeply nested values from JSON

I want to extract the coordinates from a JSON string and separate them into 2 columns, one for longitude and one for latitude.

Here is the example JSON string that sits in the column event_location:

{
    "type": "Feature",
    "geometry": {
        "type": "Point",
        "coordinates": [-99.562992, 58.096856]
    },
    "properties": {
        "timestamp": 160736582
    }
}

Seems like it would be a pretty straight forward thing but I haven't been able to get it. I have tried something like this:

select event_location ->> 'coordinates' as coordinates
from ...

But that just returns all NULL for the entire column.

I have also tried using json_populate_recordset which threw an error when I tried to type in just one row.

Any help or advice would be great.

Upvotes: 0

Views: 135

Answers (1)

Salman Arshad
Salman Arshad

Reputation: 272216

You need to use json functions/operators like so:

SELECT
    event_location->'geometry'->'coordinates'->0 AS lat,
    event_location->'geometry'->'coordinates'->1 AS lon,
    -- alternate syntax
    event_location#>'{geometry,coordinates,0}' AS lat,
    event_location#>'{geometry,coordinates,1}' AS lon
FROM t

Be advised that the datatype of the columns will be json instead of native SQL types. To get a float you could extract the value as text then convert:

SELECT
    (event_location->'geometry'->'coordinates'->>0)::numeric AS lat,
    (event_location->'geometry'->'coordinates'->>0)::numeric AS lon
FROM t

Upvotes: 1

Related Questions