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

Question

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'

klin · Accepted Answer · 2019-01-08 14:18:43Z

5

In Postgres 9.5+ you can simply use the built-in concatenation operator || to merge JSON objects.

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}}');

select (val->'a') || (val->'b') from test_js;
                  ?column?
--------------------------------------------
 {"0": 2, "1": 10, "2": 8, "3": 6, "4": 12}
(1 row)

edited Jan 8, 2019 at 14:18

klin

123k15 gold badges240 silver badges262 bronze badges

answered May 30, 2016 at 10:46

Martin Kleppmann

1,40515 silver badges10 bronze badges

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

2 Comments

eye_mew Over a year ago

what's up with ?column??

Watusimoto Over a year ago

The output column is the result of a function, and thus has no name. You could name it by adding AS new_column to the select statement.

klin · Accepted Answer · 2019-01-09 11:56:05Z

4

The answer concerns Postgres 9.4 and 9.3.

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 all
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 all
    select 1 priority, jsonb_each(val->'a') elem
    from test_js
    ) 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 all
        select 1, jsonb_each(val->'a') elem
        from test_js
        ) 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 all
        select 1, json_each(val->'a') elem
        from test_js
        ) 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.

edited Jan 9, 2019 at 11:56

answered Oct 25, 2015 at 19:55

klin

123k15 gold badges240 silver badges262 bronze badges

7 Comments

Max Hudson Over a year ago

Is this more efficient than simply CASE WHEN coalesce(a->>'1', '-1') = '-1' THEN coalesce(b->>'1', '-1') ELSE coalesce(a->>'1', '-1') END?

klin Over a year ago

Probably there is no huge difference in performance, but it's hard to imagine a query with several dozen such comparisons. The solution in the answer is simply more general.

Max Hudson Over a year ago

Purely for the sake of discussion, do you know if json objects are hashed in Postgres? If so, my CASE statement might be faster because it uses the existing hash table rather than building a new hash table for your new object? Yours probably is much faster as the number of times you reference the object increases towards infinity (mine is O(3n) and yours is O(n) where n is the number of times you query the object I believe?)

klin Over a year ago

Yes, it seems likely. For a small n simple comparison may be a bit faster. With increasing n the general query should be getting better.

mpapec Over a year ago

Can't upvote this enough, but is order by 1 necessary? Dropping leading numbers for sort and using union all should ensure first record set to be always above the second one? Ie. gist.github.com/mpapec/4dc57d58258c07d6803a846915214e0b

|

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related