postgresql - 用逗号分隔在 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;

这导致 col3_jsonb 的 jsonb 元素将值 special 设置为 separated而不是想要的 comma,separated .我知道这是按文档工作的连接运算符，但我不知道如何以不同的方式处理 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"} │
└────────────────────────────────────────────────────────┘

nullif()和 concat_ws()一种或两种情况所需的函数 "special"值缺失/空/空