postgreSQL中带有逗号分隔的jsonb元素的连接

Question

我想设计一个查询，在这里我可以以可控的方式将两个jsonb与postgreSQL中未知数量/元素集组合在一起。jsonb运算符||几乎完全符合我的目的，但对于其中一个jsonb元素，我希望将两个值连接起来并用逗号分隔，而不是让第二个jsonb的值覆盖第一个的值。例如：

'{"a":"foo", "b":"one", "special":"comma"}'::jsonb || '{"a":"bar", "special":"separated"}'::jsonb → '{"a":"bar", "b":"one", "special":"comma,separated"}'

我当前的查询如下：

INSERT INTO table AS t (col1, col2, col3_jsonb)
VALUES ("first", "second", '["a":"bar", "special":"separated"]'::jsonb))
ON CONFLICT ON CONSTRAINT unique_entries DO UPDATE
SET col3_jsonb = excluded.col3_jsonb || t.col3_jsonb
RETURNING id;

这导致用于CCD_ 2的具有值special设置为CCD_ 3而不是期望的CCD_。我知道这是一个按文档所述工作的串联运算符，但我不知道如何以不同的方式处理jsonb的一个元素，而不是尝试在查询的其他地方用WITH..子句提取特殊值。任何见解或建议都将不胜感激！

Answer 1

with t(a,b) as (values(
'{"a":"foo", "b":"one", "special":"comma"}'::jsonb,
'{"a":"bar", "special":"separated"}'::jsonb))
select
a || jsonb_set(b, '{special}',
to_jsonb(concat_ws(',', nullif(a->>'special', ''), nullif(b->>'special', ''))))
from t;
┌────────────────────────────────────────────────────────┐
│                        ?column?                        │
├────────────────────────────────────────────────────────┤
│ {"a": "bar", "b": "one", "special": "comma,separated"} │
└────────────────────────────────────────────────────────┘

CCD_ 6和CCD_ 7函数，用于一个或两个CCD_

Answer 2

您可以对两个值使用jsonb_each，然后使用jsonb_object_agg将它们放回对象中：

…
SET col3_jsonb = (
SELECT jsonb_object_agg(
key,
COALESCE(to_jsonb((old.value->>0) || ',' || (new.value->>0)), new.value, old.value)
)
FROM jsonb_each(example.old_obj) old
FULL OUTER JOIN jsonb_each(example.new_obj) new USING (key)
)

^{(在线演示(}

将任意JSON值强制转换为可连接字符串需要技巧。如果您知道所有对象属性都有字符串值，则可以使用jsonb_each_text进行简化：

SELECT jsonb_object_agg(
key,
COALESCE(old.value || ',' || new.value, new.value, old.value)
)
FROM jsonb_each_text(example.old_obj) old
FULL OUTER JOIN jsonb_each_text(example.new_obj) new USING (key)