2

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"} }
1
  • So what exactly are you trying to achieve? jsonb_set is probably the closest you can get (don't really know what that Oracle function does). But without more details, this is really hard to answer. Please edit your question (by clicking on the edit link below it) and add some sample data and the expected output based on that data as formatted text. See here for some tips on how to create nice looking text tables. Commented Aug 10, 2020 at 20:51

4 Answers 4

3

Leaving my 2 cents for a more compact solution here, based on this post:

CREATE OR REPLACE FUNCTION json_merge_patch("target" jsonb, "patch" jsonb) RETURNS jsonb AS $$
    BEGIN
        RETURN COALESCE(jsonb_object_agg(
            COALESCE("tkey", "pkey"),
            CASE
                WHEN "tval" ISNULL THEN "pval"
                WHEN "pval" ISNULL THEN "tval"
                WHEN jsonb_typeof("tval") != 'object' OR jsonb_typeof("pval") != 'object' THEN "pval"
                ELSE json_merge_patch("tval", "pval")
            END
        ), '{}'::jsonb)
          FROM jsonb_each("target") e1("tkey", "tval")
     FULL JOIN jsonb_each("patch") e2("pkey", "pval")
            ON "tkey" = "pkey"
         WHERE jsonb_typeof("pval") != 'null'
            OR "pval" ISNULL;
    END;
$$ LANGUAGE plpgsql;

As far as I'm concerned, it follows the RFC 7396.

Sign up to request clarification or add additional context in comments.

Comments

2

The spec is simple enough to follow with recursion.

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;

Testing with the example in the 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)

Testing with the example in your question:

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)

3 Comments

The snippet works for most examples. What I just spottet is that it is not possible to add a nested json doc with an update. E.g. original '{"foo": "bar"}' and update with '{"foo": "bar", {"foo2": "bar2"}}'. The nested json is just not considered.
@Janukowitsch Your example is not clear. That is not valid JSON.
Oh yes. You are right. I actually implemented the solution based on the tests from the example 3 from the rfc. During testing I realized there was a missing case and I just wanted to share here as well. The missing test case is: ` '{"foo": "bar"}' and update with '{"foo": "bar", "added-key": {"foo2": "bar2"}}'`. Actually, I decided to do the patch in the application and just select the respective row with for update, so I do not have a fix for the above. Of course this solution might not be optimal for everyone.
2

None of the currently posted answers works fully correctly, they fail on some of the tests from RFC 7396 (https://www.rfc-editor.org/rfc/rfc7396#section-2).

Here is a solution that passes all RFC 7396 test cases, structurally based on jsonb_recursive_merge from accepted answer in question Merging JSONB values in PostgreSQL?.

CREATE OR REPLACE FUNCTION jsonb_mergepatch(
  target jsonb, -- target JSON value
  patch jsonb -- patch JSON value
)
RETURNS jsonb LANGUAGE plpgsql IMMUTABLE AS
$$
BEGIN
  -- If the patch is not a JSON object, return the patch as the result (base case)
  IF patch isnull or jsonb_typeof(patch) != 'object' THEN
    RETURN patch;
  END IF;

  -- If the target is not an object, set it to an empty object
  IF target isnull or jsonb_typeof(target) != 'object' THEN
    target := '{}';
  END IF;

  RETURN coalesce(
    jsonb_object_agg(
      coalesce(targetKey, patchKey), -- there will be either one or both keys equal
      CASE
        WHEN patchKey isnull THEN targetValue -- key missing in patch - retain target value
        ELSE jsonb_mergepatch(targetValue, patchValue) 
      END
    ),
    '{}'::jsonb -- if SELECT will return no keys (empty table), then jsonb_object_agg will return NULL, need to return {} in that case
  )
  FROM jsonb_each(target) temp1(targetKey, targetValue)
    FULL JOIN jsonb_each(patch) temp2(patchKey, patchValue) ON targetKey = patchKey
  WHERE jsonb_typeof(patchValue) != 'null' OR patchValue isnull; -- remove keys which are set to null in patch object
END;
$$;

The correctness can be tested by running test cases from RFC 7396 against above function:

with tests (a, b, expected_result) as (
  values
    -- RFC 7396 test cases: https://www.rfc-editor.org/rfc/rfc7396#appendix-A
    ('{"a": "b"}'::jsonb,           '{"a": "c"}'::jsonb,                   '{"a": "c"}'::jsonb          ),
    ('{"a": "b"}'::jsonb,           '{"b": "c"}'::jsonb,                   '{"a": "b", "b": "c"}'::jsonb),
    ('{"a": "b"}'::jsonb,           '{"a": null}'::jsonb,                  '{}'::jsonb                  ),
    ('{"a": "b", "b": "c"}'::jsonb, '{"a": null}'::jsonb,                  '{"b": "c"}'::jsonb          ),
    ('{"a": ["b"]}'::jsonb,         '{"a": "c"}'::jsonb,                   '{"a": "c"}'::jsonb          ),
    ('{"a": "c"}'::jsonb,           '{"a": ["b"]}'::jsonb,                 '{"a": ["b"]}'::jsonb        ),
    ('{"a": {"b": "c"}}'::jsonb,    '{"a": {"b": "d", "c": null}}'::jsonb, '{"a": {"b": "d"}}'::jsonb   ),
    ('{"a": [{"b": "c"}]}'::jsonb,  '{"a": [1]}'::jsonb,                   '{"a": [1]}'::jsonb          ),
    ('["a", "b"]'::jsonb,           '["c", "d"]'::jsonb,                   '["c", "d"]'::jsonb          ),
    ('{"a": "b"}'::jsonb,           '["c"]'::jsonb,                        '["c"]'::jsonb               ),
    ('{"a": "foo"}'::jsonb,         'null'::jsonb,                         'null'::jsonb                ),
    ('{"a": "foo"}'::jsonb,         '"bar"'::jsonb,                        '"bar"'::jsonb               ),
    ('{"e": null}'::jsonb,          '{"a": 1}'::jsonb,                     '{"e": null, "a": 1}'::jsonb ),
    ('[1, 2]'::jsonb,               '{"a": "b", "c": null}'::jsonb,        '{"a": "b"}'::jsonb          ),
    ('{}'::jsonb,                   '{"a": {"bb": {"ccc": null}}}'::jsonb, '{"a": {"bb": {}}}'::jsonb   )
)
select tests.*,
    jsonb_mergepatch(a, b) as actual_result,
    jsonb_mergepatch(a, b)::varchar = expected_result::varchar as test_passed 
from tests

Comments

0

For the JSONB fields you can use the concatenation operation ||. Here is a sample that I made to make an upsert with merging of field:

INSERT INTO table_name (id, title, po_document)
VALUES ($1, $2, $3::JSONB)
ON CONFLICT (id) DO
    UPDATE
    SET title = COALESCE(NULLIF($2, ''),  table_name.title),
        props = table_name.props || $3::JSONB

Here I have a table with a document id, title and it's properties in JSONB. When creating a document I can pass all the 3 values. To update the only title I can pass only it in a second title parameter and set the third props parameter to an empty object {}. To update the properties I can leave the second parameter title empty (then the current value will remain) and set the third param with an object that contains only fields that should be updated. To remove a field value you can explicitly set it to null

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.