在postgres中查询json [英] Querying json in postgres

问题描述

我必须从包含空间信息的json文件中提取数据.该文件的内容是

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)

问题是我不能那样使用它.我只想捕获"lat"和"lon"的值以将它们放在字段表中

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

我尝试使用功能json_to_recordset失败

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.

推荐答案

您可以使用

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);

有了它，您可以简单地创建一个表(或在现有表中使用INSERT):

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:LATERAL是隐式的，因为LATERAL关键字对于返回集合的函数调用是可选的，但是您可以使它真正地显式，例如:

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);

此外，尽管您使用json_array_elements时，肯定高于9.3，但仅限9.3 +.

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

这篇关于在postgres中查询json的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！