时间戳

首页 > 解决方案 > 为每条记录解析一个带有 FOR 循环的 JSON 列？

json - 为每条记录解析一个带有 FOR 循环的 JSON 列？

问题描述

作为 Postgres 和 SQL 的新手，我在 Postgres 9.6 中有以下场景：

table1

"Myjson" JSON
"DateOfAcquisition"  DATE
"Id" INT

里面有一个列表，我用"Myjson"循环遍历它FOR。

我的目标是将每个 json 列表的元素table1放在另一个表中：

table2

jsonelem1 INT
jsonelem2 INT
"DateOfAcquisition" DATE
"Id" INT

我已经编写了以下代码来解析 json 字段，但我不确定如何为for第一个表的每条记录运行循环。

    DO
$BODY$
DECLARE
    omgjson json := myjsonfield; -- this should change for every row of the first table
    i json;
    myJsonelem1 INT;
    myJsonelem2 INT;

begin

FOR i IN SELECT * FROM json_array_elements(omgjson)
  loop
    myJsonelem2 i->>  'jsonsubfield'::INT;

    INSERT INTO destinationTable VALUES (myJsonelem2,DateOfAcquisition);
END LOOP;

END;
$BODY$ language plpgsql

标签： jsonpostgresqlfor-loopplpgsqlset-returning-functions

解决方案

基于集合的方法通常比循环快得多（并且更短且不易出错）。

基于您的答案设置，这与您的问题有很大不同。

INSERT INTO table2(myfield, data)  -- cleaner: explicit target columns
SELECT k."myField", j.i ->> 'data'
FROM   table1 k, json_array_elements(k."ScrapedJson" -> 'calendar_days') j(i)
WHERE  NOT k."HasBeenProcessed"
-- ORDER BY ???  -- you might want to order rows favorably?

隐式LATERAL连接是这里的关键技术。

... FROM   table1 k, json_array_elements(...) ...

简称：

... FROM   table1 k
    CROSS  JOIN LATERAL json_array_elements(...) ...

显然，"ScrapedJson" -> 'calendar_days'是 JSON 数组，而不是您在问题中写的“列表”。

有关的：

旁白

帮自己一个忙，避免在 Postgres 中使用双引号的 CaMeL 案例名称。您的问题和答案中引用和未引用标识符的狂野混合只会让人感到困惑。我在您的问题中引用了列名，以在某种程度上与您的答案相匹配。我的长期建议：只使用合法的、小写的、未引用的名称。

考虑数据类型jsonb而不是json大多数工作负载。

推荐阅读