postgresql - There is a similar function json_merge_patch() in Postgres as in Oracle

Question

My endpoint, accepts client request HTTP method PATCH, a payload content type of JSON Merge Patch (RFC 7396). https://www.rfc-editor.org/rfc/rfc7396

We used Oracle and it was very convenient to update json content in the database, used function json_merge_patch()

UPDATE table_name SET po_document =
  json_mergepatch(po_document, json_by_rfc7396);

https://docs.oracle.com/en/database/oracle/oracle-database/19/adjsn/updating-json-document-json-merge-patch.html

I have not found a similar function in Postgres, jsonb_set() and operators || and #-, not convenient for deep patches of json content.

What's the PostgreSQL best practice for deep patching json content?

Example:

SELECT json_merge_patch(
   '{"root": {"k1": "v1", "k2": "v2"} }'::jsonb, -- source JSON
   '{"root": {"k1": "upd", "k2": null, "k3": "new"} }'::jsonb -- JSON patch (RFC 7396)
)

Output

{"root": {"k1": "upd","k3": "new"} }

标签： postgresqljsonb

Answer 1

该规范很简单，可以遵循递归。

create or replace function jsonb_merge_patch(v_basedoc jsonb, v_patch jsonb) 
returns jsonb as $$                                            
with recursive patchexpand as(                                                        
  select '{}'::text[] as jpath, v_patch as jobj, jsonb_typeof(v_patch) as jtype, 0 as lvl
  union all
  select p.jpath||o.key as jpath, p.jobj->o.key as jobj, jsonb_typeof(p.jobj->o.key) as jtype, p.lvl + 1 as lvl
    from patchexpand p
         cross join lateral jsonb_each(case when p.jtype = 'object' then p.jobj else '{}'::jsonb end) as o(key, value)
), pathnum as (
  select *, row_number() over (order by lvl, jpath) as rn
    from patchexpand
), apply as (
  select case                          
           when jsonb_typeof(v_basedoc) = 'object' then v_basedoc 
           else '{}'::jsonb
         end as basedoc, 
         p.rn
    from pathnum p
   where p.rn = 1
  union all                                                                          
  select case
           when p.jtype = 'object' then a.basedoc
           when p.jtype = 'null' then a.basedoc #- p.jpath
           else jsonb_set(a.basedoc, p.jpath, p.jobj)
         end as basedoc,                                                                                              
         p.rn
    from apply a                                         
         join pathnum p
           on p.rn = a.rn + 1
)                       
select case 
         when jsonb_typeof(v_patch) != 'object' then v_patch 
         else basedoc 
       end
  from apply 
 order by rn desc 
 limit 1;                                                 
$$                                                   
language sql;

使用 RFC 中的示例进行测试：

select jsonb_pretty(jsonb_merge_patch('{
     "title": "Goodbye!",
     "author" : {
       "givenName" : "John",
       "familyName" : "Doe"
     },
     "tags":[ "example", "sample" ],
     "content": "This will be unchanged"
   }'::jsonb,
'{
     "title": "Hello!",
     "phoneNumber": "+01-123-456-7890",
     "author": {
       "familyName": null
     },
     "tags": [ "example" ]
   }'::jsonb));

               jsonb_pretty               
------------------------------------------
 {                                       +
     "tags": [                           +
         "example"                       +
     ],                                  +
     "title": "Hello!",                  +
     "author": {                         +
         "givenName": "John"             +
     },                                  +
     "content": "This will be unchanged",+
     "phoneNumber": "+01-123-456-7890"   +
 }
(1 row)

使用您问题中的示例进行测试：

SELECT jsonb_merge_patch(
   '{"root": {"k1": "v1", "k2": "v2"} }'::jsonb, -- source JSON
   '{"root": {"k1": "upd", "k2": null, "k3": "new"} }'::jsonb -- JSON patch (RFC 7396)
);

          jsonb_merge_patch           
--------------------------------------
 {"root": {"k1": "upd", "k3": "new"}}
(1 row)