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

jsonpostgresql

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'

Best Answer

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
Related Question