Querying json in postgres

Question

I have to extract data from a json file who contains spatial information. The content of this file is

{"vertices":[{"lat":46.744628268759314,"lon":6.569952920654968},
         {"lat":46.74441692818192,"lon":6.570487107359068},
         {"lat":46.74426116111054,"lon":6.570355867853787},
         {"lat":46.74447250168793,"lon":6.569821681149689}],
"name":"demo-field",
"cropType":"sugarbeet",
"cropPlantDistance":0.18000000715255737,
"rowDistance":0.5,"numberOfRows":[28,12,12],"seedingDate":"2016-08-17T07:39+00:00"}

I've created a table then copied the content of this file into it

create table field(data json);

COPY field(data) FROM '/home/guest-pc5/field.json';

I now I can query my data

SELECT json_array_elements(data->'vertices') from field;
 {"lat":46.744628268759314,"lon":6.569952920654968}
 {"lat":46.74441692818192,"lon":6.570487107359068}
 {"lat":46.74426116111054,"lon":6.570355867853787}
 {"lat":46.74447250168793,"lon":6.569821681149689}
(4 rows)

The problem is that I can't use it like that. I would like to catch only values of "lat" and "lon" to put them in the field table

I've tried to use the function json_to_recordset without success

    select * from json_to_recordset('[{"lat":46.744628268759314,"lon":6.569952920654968},{"lat":46.74441692818192,"lon":6.570487107359068},{"lat":46.74426116111054,"lon":6.570355867853787},{"lat":46.74447250168793,"lon":6.569821681149689}]') as (lat numeric, lon numeric);
    ERROR:  function json_to_recordset(unknown) does not exist
    LINE 1: select * from json_to_recordset('[{"lat":46.744628268759314,...
                      ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

This exact query works for me in PostgreSQL 9.5

Boris Schegolev
– Boris Schegolev

2016-08-25 11:03:28 +00:00
Commented Aug 25, 2016 at 11:03 — Boris Schegolev
– Boris Schegolev, Commented Aug 25, 2016 at 11:03

MatheusOl · Accepted Answer · 2016-08-25 11:42:40Z

You can use json manipulator operator ->> to get the value you want as text out of json_array_elements output. To make it easier, you can call json_array_elements in FROM clause (which is a lateral call to a set-returning function):

SELECT
    f.data AS original_json,
    CAST((e.element->>'lat') AS numeric) AS lat,
    CAST((e.element->>'lon') AS numeric) AS lon
FROM
    field AS f,
    json_array_elements(f.data->'vertices') AS e(element);

With that, you can simple create a table (or use INSERT into an existent one):

CREATE TABLE coordinates AS
SELECT
    f.data AS original_json,
    CAST((e.element->>'lat') AS numeric) AS lat,
    CAST((e.element->>'lon') AS numeric) AS lon
FROM
    field AS f,
    json_array_elements(f.data->'vertices') AS e(element);

OBS: The LATERAL there is implicit, as the LATERAL keyword is optional for set-returning function calls, but you could make it really explicit, as:

FROM
    field f
    CROSS JOIN LATERAL json_array_elements(f.data->'vertices') AS e(element);

Also, LATERAL is 9.3+ only, although you are certainly above that as you are using json_array_elements (also 9.3+ only).

Collectives™ on Stack Overflow

Querying json in postgres

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related