PostgreSQL：高效地将JSON数组拆分为行

Question

PostgreSQL：高效地将JSON数组拆分为行

sqljsonpostgresql

4

我有一个包含JSON编码数据的文本列的表格（Table A)。

JSON数据始终是一个包含一到数千个普通对象的数组。

我还有另一个表格(Table B)，其中包括一个数据类型为'JSON'的列。

我想要从Table A中选择所有行，将JSON数组分割成其元素，并将每个元素插入到Table B中。

额外目标：每个对象（几乎都）有一个名为x的键。我想要将x的值提取到列中，并从原始对象中删除x（如果存在的话）。

例如：Table A

| id | json_array (text)              |
+----+--------------------------------+
|  1 | '[{"x": 1}, {"y": 8}]'         |
|  2 | '[{"x": 2, "y": 3}, {"x": 1}]' |
|  3 | '[{"x": 8, "z": 2}, {"z": 3}]' |
|  4 | '[{"x": 5, "y": 2, "z": 3}]'   |

...将变为：表格 B

| id | a_id | x    | json (json)        |
+----+------+------+--------------------+
|  0 |    1 |    1 | '{}'               |
|  1 |    1 | NULL | '{"y": 8}'         |
|  2 |    2 |    2 | '{"y": 3}'         |
|  3 |    2 |    1 | '{}'               |
|  4 |    3 |    8 | '{"y": 2}'         |
|  5 |    3 | NULL | '{"z": 3}'         |
|  6 |    4 |    5 | '{"y": 2, "z": 3}' |

这最初需要处理数百万行数据，并且需要定期运行，因此提高效率是优先考虑的。

不使用循环和PL/PgSQL是否有可能实现？我一直没有取得太大进展。

- aidan

你能使用 jsonb 吗？相比于 json 数据类型，jsonb 有更多的选项来操作对象。使用 jsonb 删除 x 很容易，但是使用 json 则更加复杂。 - Patrick

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Patrick · Accepted Answer

json 数据类型并不适合（或者说并不是为了）在数据库层面进行修改。提取 JSON 对象中的 "x" 对象因此很麻烦，虽然也是可以做到的。

你应该创建一个表 B （列名最好比 "json" 更有创意；这里我用的是 item），并将 id 列设为 serial 并从 0 开始。一个纯 json 解决方案，看起来像这样：

INSERT INTO b (a_id, x, item)
  SELECT sub.a_id, sub.x,
         ('{' ||
         string_agg(
             CASE WHEN i.k IS NULL THEN '' ELSE '"' || i.k || '":' || i.v END,
             ', ') ||
         '}')::json
  FROM (
    SELECT a.id AS a_id, (j.items->>'x')::integer AS x, j.items
    FROM a, json_array_elements(json_array) j(items) ) sub
  LEFT JOIN json_each(sub.items) i(k,v) ON i.k <> 'x'
  GROUP BY sub.a_id, sub.x
  ORDER BY sub.a_id;

在子查询中，这提取了a_id和x值，以及JSON对象。在外部查询中，将JSON对象拆分为各个部分，并将具有键x的对象排除（LEFT JOIN ON i.k <> 'x'）。在选择列表中，通过字符串连接将这些部分再次组合成复合对象并分组。

这必须像这样，因为json没有任何重要的内置操作函数。此解决方案适用于PG 9.3+版本，即就JSON支持而言的时间不可考。

如果您使用的是PG9.5+，那么通过转换为jsonb，解决方案将变得更加简单：

INSERT INTO b (a_id, x, item)
  SELECT a.id, (j.items->>'x')::integer, j.items #- '{x}'
  FROM a, jsonb_array_elements(json_array::jsonb) j(items);

#-操作符在jsonb数据类型上进行所有的繁杂工作。显然，在幕后有很多工作正在进行，将json转换为jsonb，因此如果您发现需要更频繁地操作JSON对象，则最好一开始就使用jsonb类型。建议在您的情况下使用EXPLAIN ANALYZE SELECT ...对大约10,000行进行一些基准测试，以查看哪种方法适合您的设置。（在测试时可以放心忘记INSERT）。