Skip to content

Instantly share code, notes, and snippets.

@kizdolf
Created September 1, 2022 14:51
Show Gist options
  • Save kizdolf/b1ade7a9cf05c89cefb925d87dfe48ff to your computer and use it in GitHub Desktop.
Save kizdolf/b1ade7a9cf05c89cefb925d87dfe48ff to your computer and use it in GitHub Desktop.
jsonb_recursive_merge
-- 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"]}}
*/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment