maxhud maxhud - 6 months ago 156
SQL Question

Postgres function to merge two json objects with overlapping keys into one object

I've got the following json object:

{
"a" : {
"0" : 2,
"1" : 4,
"3" : 6,
}
"b" : {
"2" : 8,
"1" : 10, /*note this key exists in "a" too*/
"4" : 12,
}
}


I'd like to generate the following object and then be able to extract an element from it like so:

{
"0" : 2,
"1" : 10,
"2" : 8,
"3" : 6,
"4" : 12,
}


Extraction:
object->>'1'
should return
'10'


Basically I have two arrays with potentially overlapping keys and I want to merge the two, giving one array precedence.

How can I accomplish this? Ideally I'd call a function like
arrayMerge(a, b)
and it gave
'a'
higher precedence than
'b'

Answer

Example data:

create table test_js(val jsonb);
insert into test_js values ('{"a":{"0":2,"1":4,"3":6},"b":{"1":10,"2":8,"4":12}}');

First, retrieve all pairs with arbitrary chosen priorities:

select 0 priority, jsonb_each(val->'b') elem
from test_js
union
select 1 priority, jsonb_each(val->'a') elem
from test_js
order by 1

 priority |  elem  
----------+--------
        0 | (1,10)
        0 | (2,8)
        0 | (4,12)
        1 | (0,2)
        1 | (1,4)
        1 | (3,6)
(6 rows)

Next, from the resultset select elements with unique keys:

select distinct on ((elem).key) elem
from (
    select 0 priority, jsonb_each(val->'b') elem
    from test_js
    union
    select 1 priority, jsonb_each(val->'a') elem
    from test_js
    order by 1
    ) sub

  elem  
--------
 (0,2)
 (1,10)
 (2,8)
 (3,6)
 (4,12)
(5 rows)    

Finally, aggregate the result into a json object:

select json_object_agg((elem).key, (elem).value) result
from (
    select distinct on ((elem).key) elem
    from (
        select 0, jsonb_each(val->'b') elem
        from test_js
        union
        select 1, jsonb_each(val->'a') elem
        from test_js
        order by 1
        ) sub
    ) sub

                      result                       
---------------------------------------------------
 { "0" : 2, "1" : 10, "2" : 8, "3" : 6, "4" : 12 }
(1 row)

In Postgres 9.3 you can simulate json_object_agg using string_agg:

select format('{ %s }', 
    string_agg(format('"%s" : %s', (elem).key, (elem).value), ', '))::json result
from (
    select distinct on ((elem).key) elem
    from (
        select 0, json_each(val->'b') elem
        from test_js
        union
        select 1, json_each(val->'a') elem
        from test_js
        order by 1
        ) sub
    ) sub

                      result                       
---------------------------------------------------
 { "0" : 2, "1" : 10, "2" : 8, "3" : 6, "4" : 12 }
(1 row)     

Btw, your json value is invalid, should be

{
    "a": {
        "0": 2,
        "1": 4,
        "3": 6
    },
    "b": {
        "1": 10,
        "2": 8,
        "4": 12
    }
}

Please, use JSONLint to validate json values.