Robin Robin - 1 year ago 103
JSON Question

Merging Concatenating JSON(B) columns in query

Using Postgres 9.4, I am looking for a way to merge two (or more)

columns in a query. Consider the following table as an example:

id | json1 | json2
1 | {'a':'b'} | {'c':'d'}
2 | {'a1':'b2'} | {'f':{'g' : 'h'}}

Is it possible to have the query return the following:

id | json
1 | {'a':'b', 'c':'d'}
2 | {'a1':'b2', 'f':{'g' : 'h'}}

Unfortunately, I can't define a function as described here. Is this possible with a "traditional" query?

Answer Source

Here is the complete list of build-in functions that can be used to create json objects in PostgreSQL.

  • row_to_json and json_object doest not allow you to define your own keys, so it can't be used here
  • json_build_object expect you to know by advance how many keys and values our object will have, that's the case in your example, but should not be the case in the real world
  • json_object looks like a good tool to tackle this problem but it forces us to cast our values to text so we can't use this one either

Well... ok, wo we can't use any classic functions.

Let's take a look at some aggregate functions and hope for the best...

json_object_agg Is the only aggregate function that build objects, that's our only chance to tackle this problem. The trick here is to find the correct way to feed the json_object_agg function.

Here is my test table and data

  json1 JSONB,
  json2 JSONB

INSERT INTO test (json1, json2) VALUES
  ('{"a":"b", "c":"d"}', '{"e":"f"}'),
  ('{"a1":"b2"}', '{"f":{"g" : "h"}}');

And after some trials and errors with json_object here is a query you can use to merge json1 and json2 in PostgreSQL 9.4

WITH all_json_key_value AS (
  SELECT id, t1.key, t1.value FROM test, jsonb_each(json1) as t1
  SELECT id, t1.key, t1.value FROM test, jsonb_each(json2) as t1
SELECT id, json_object_agg(key, value) 
FROM all_json_key_value 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download