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'

Have a look at Merging Concatenating JSON(B) columns in query over at StackOverflow — Bergi
– Bergi, Commented Sep 15, 2019 at 3:22

Bruno · Accepted Answer · 2016-02-17 22:52:56Z

20

If you're using PostgreSQL 9.5 (at least), you can use the concatenation operator (||) on jsonb types (and you can convert json to jsonb if necessary first).

For example:

WITH test(data) AS (
    VALUES ('{
        "a" : {
            "0" : 2,
            "1" : 4,
            "3" : 6
        },
        "b" : {
            "2" : 8,
            "1" : 10,
            "4" : 12
        }
    }'::jsonb)
)
SELECT (data->'a') || (data->'b') FROM test

will produce:

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

(Please note that, in this particular example, the parentheses around (data->'a') matter.)

You can modify the example above to get a specific value, as you requested, for example:

SELECT (((data->'a') || (data->'b'))->'1')::text::integer FROM test

answered Feb 17, 2016 at 22:52

Bruno

1,6623 gold badges18 silver badges31 bronze badges

Thanks for your comment about the the parentheses around (data->'a') which really matter! It saved me a few minutes more of head scratching

Christiaan Westerbeek
– Christiaan Westerbeek

2021-02-03 21:15:15 +00:00
Commented Feb 3, 2021 at 21:15

Add a comment |

Stack Exchange Network

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

1 Answer 1

Your Answer

Hot Network Questions

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

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions