您可以使用侧面调用

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