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'

• Kassandry: What version of PostgreSQL are you using?
• Bergi: Have a look at Merging Concatenating JSON(B) columns in query over at StackOverflow

Answer 1

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

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