jsonb_recursive_merge

jsonb_recursive_merge.sql

-- from https://medium.com/hootsuite-engineering/recursively-merging-jsonb-in-postgresql-efd787c9fad7 
-- with unique array values merge

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) = 'array' AND jsonb_typeof(vb) = 'array' THEN 
          (SELECT array_to_json(array_agg(DISTINCT v.value)) FROM jsonb_array_elements_text(va || vb) v)::jsonb
        WHEN jsonb_typeof(va) <> 'object' OR jsonb_typeof(vb) <> 'object' THEN vb
        ELSE jsonb_recursive_merge(va, vb) END 
    )  
  FROM jsonb_each(A) temptable1(ka, va)
  FULL JOIN jsonb_each(B) temptable2(kb, vb) ON ka = kb 
$$;

/*
select * from jsonb_recursive_merge('{"desktop": {"default": ["1"]}}', '{"desktop": {"default": ["2", "1"]}}');
jsonb_recursive_merge         
--------------------------------------
{"desktop": {"default": ["1", "2"]}}

select * from jsonb_recursive_merge('{"desktop": {"default": ["1", "3"]}}', '{"desktop": {"default": ["2", "1"]}}');
jsonb_recursive_merge           
-------------------------------------------
{"desktop": {"default": ["1", "2", "3"]}}

select * from jsonb_recursive_merge('{"desktop": {"default": ["1", "3"]}}', '{"desktop": {"default": ["5", "1"]}}');
jsonb_recursive_merge           
-------------------------------------------
{"desktop": {"default": ["1", "3", "5"]}}

select * from jsonb_recursive_merge('{"desktop": {"default": ["1", "3"]}}', '{"desktop": {"default": ["5", "1", "2"]}}');
jsonb_recursive_merge              
------------------------------------------------
{"desktop": {"default": ["1", "2", "3", "5"]}}

select * from jsonb_recursive_merge('{"desktop": {"default": ["1", "3"], "f": ["m1"] } }', '{"desktop": {"default": ["5", "1", "2"]}}');
jsonb_recursive_merge                    
-------------------------------------------------------------
{"desktop": {"f": ["m1"], "default": ["1", "2", "3", "5"]}}

select * from jsonb_recursive_merge('{"desktop": {"default": ["1", "3"], "f": ["m1"] },"mobile":{"default": ["md1"], "f": ["mf1"]} }', '{"desktop": {"default": ["5", "1", "2"]}, "mobile":{"default": ["md2"]}}');
                                              jsonb_recursive_merge                                               
------------------------------------------------------------------------------------------------------------------
{"mobile": {"f": ["mf1"], "default": ["md1", "md2"]}, "desktop": {"f": ["m1"], "default": ["1", "2", "3", "5"]}}

*/