The json
数据类型不是特别适合(或不适合)在数据库级别进行修改。提取"x"
因此,尽管可以完成,但从 JSON 对象获取对象很麻烦。
您应该创建表 B(希望使用比"json"
;我在用item
在这里)并制作id
a栏serial
从 0.A 开始pure 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
and 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);
The #-
上的运算符jsonb
数据类型在这里完成所有脏工作。显然,幕后正在进行大量工作,将json
to jsonb
,因此,如果您发现需要更频繁地操作 JSON 对象,那么最好使用jsonb
首先键入。对于你的情况,我建议你做一些基准测试EXPLAIN ANALYZE SELECT ...
(你可以放心地忘记INSERT
在测试时)大约 10,000 行,看看哪一个最适合您的设置。