合并PostgreSQL中的JSONB值? [英] Merging JSONB values in PostgreSQL?

问题描述

使用||运算符将产生以下结果:

Using the || operator yields the following result:

select '{"a":{"b":2}}'::jsonb || '{"a":{"c":3}}'::jsonb ;
    ?column?     
-----------------
 {"a": {"c": 3}}
(1 row)

我希望能够实现以下结果(??只是操作员的占位符):

I would like to be able to do achieve the following result (?? just a placeholder for the operator):

select '{"a":{"b":2}}'::jsonb ?? '{"a":{"c":3}}'::jsonb ;
    ?column?     
-----------------
 {"a": {"b": 2, "c": 3}}
(1 row)

因此，您可以看到顶级a键的子值已合并"，因此结果同时包含b和c.

So, you can see the top-level a key has its child values "merged" such that the result contains both b and c.

如何在Postgres中深度"合并两个 JSONB 值?

How do you "deep" merge two JSONB values in Postgres?

有可能吗?

一个更复杂的测试用例:

A more complex test case:

select '{"a":{"b":{"c":3},"z":true}}'::jsonb ?? '{"a":{"b":{"d":4},"z":false}}'::jsonb ;
    ?column?     
-----------------
 {"a": {"b": {"c": 3, "d": 4}, "z": false}}
(1 row)

另一个测试案例，其中原语合并"到对象:

Another test case where a primitive "merges over" and object:

select '{"a":{"b":{"c":3},"z":true}}'::jsonb ?? '{"a":{"b":false,"z":false}}'::jsonb ;
        ?column?         
-----------------
 {"a": {"b": false, "z": false}}
(1 row)

推荐答案

对于两个值，都应使用jsonb_each()合并未嵌套的元素.在不平凡的查询中执行此操作可能会感到不舒服，因此我更喜欢这样的自定义函数:

You should merge unnested elements using jsonb_each() for both values. Doing this in a non-trivial query may be uncomfortable, so I would prefer a custom function like this one:

create or replace function jsonb_my_merge(a jsonb, b jsonb)
returns jsonb language sql as $$
    select 
        jsonb_object_agg(
            coalesce(ka, kb), 
            case 
                when va isnull then vb 
                when vb isnull then va 
                else va || vb 
            end
        )
    from jsonb_each(a) e1(ka, va)
    full join jsonb_each(b) e2(kb, vb) on ka = kb
$$;

使用:

select jsonb_my_merge(
    '{"a":{"b":2}, "d": {"e": 10}, "x": 1}'::jsonb, 
    '{"a":{"c":3}, "d": {"f": 11}, "y": 2}'::jsonb
)

                          jsonb_my_merge                          
------------------------------------------------------------------
 {"a": {"b": 2, "c": 3}, "d": {"e": 10, "f": 11}, "x": 1, "y": 2}
(1 row)

---

您可以使用递归来稍微修改函数，以使解决方案适用于任何级别的嵌套:

---

You can slightly modify the function using recursion to get a solution working on any level of nesting:

create or replace function jsonb_recursive_merge(a jsonb, b jsonb)
returns jsonb language sql as $$
    select 
        jsonb_object_agg(
            coalesce(ka, kb), 
            case 
                when va isnull then vb 
                when vb isnull then va 
                when jsonb_typeof(va) <> 'object' then va || vb
                else jsonb_recursive_merge(va, vb)
            end
        )
    from jsonb_each(a) e1(ka, va)
    full join jsonb_each(b) e2(kb, vb) on ka = kb
$$;

示例:

select jsonb_recursive_merge( 
    '{"a":{"b":{"c":3},"x":5}}'::jsonb, 
    '{"a":{"b":{"d":4},"y":6}}'::jsonb);

             jsonb_recursive_merge              
------------------------------------------------
 {"a": {"b": {"c": 3, "d": 4}, "x": 5, "y": 6}}
(1 row)

select jsonb_recursive_merge(
    '{"a":{"b":{"c":{"d":{"e":1}}}}}'::jsonb, 
    '{"a":{"b":{"c":{"d":{"f":2}}}}}'::jsonb)

            jsonb_recursive_merge             
----------------------------------------------
 {"a": {"b": {"c": {"d": {"e": 1, "f": 2}}}}}
(1 row)

---

最后，OP提出的具有更改功能的变体(请参见下面的评论):

---

Finally, the variant of the function with changes proposed by OP (see comments below):

create or replace function jsonb_recursive_merge(a jsonb, b jsonb) 
returns jsonb language sql as $$ 
select 
    jsonb_object_agg(
        coalesce(ka, kb), 
        case 
            when va isnull then vb 
            when vb isnull then va 
            when jsonb_typeof(va) <> 'object' or jsonb_typeof(vb) <> 'object' then vb 
            else jsonb_recursive_merge(va, vb) end 
        ) 
    from jsonb_each(a) e1(ka, va) 
    full join jsonb_each(b) e2(kb, vb) on ka = kb 
$$;

这篇关于合并PostgreSQL中的JSONB值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！