合并查询中的串联JSON（B）列

Question

我正在使用Postgres 9.4寻找一种在查询中合并两个（或更多）json或jsonb列的方法。以下表为例：

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

查询是否可能返回以下内容：

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

不幸的是，我无法按此处所述定义函数。使用“传统”查询是否有可能？

Answer 1

这是可用于在PostgreSQL中创建json对象的内置函数的完整列表。http://www.postgresql.org/docs/9.4/static/functions-
json.html

• row_to_json并且json_object不允许您定义自己的密钥，因此不能在此处使用
• json_build_object 希望您提前知道我们的对象将具有多少个键和值，在您的示例中就是这种情况，但在现实世界中并非如此
• json_object 看起来是解决此问题的好工具，但它迫使我们将价值观转化为文字，因此我们也无法使用

好吧…好吧，我们不能使用任何经典功能。

让我们看一些聚合函数，并希望获得最好的… http://www.postgresql.org/docs/9.4/static/functions-
aggregate.html

json_object_agg是构建对象的唯一聚合函数，这是我们解决此问题的唯一机会。这里的窍门是找到正确的json_object_agg函数功能。

这是我的测试表和数据

CREATE TABLE test (
  id    SERIAL PRIMARY KEY,
  json1 JSONB,
  json2 JSONB
);

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

经过一些试验和错误之后，json_object您可以在PostgreSQL 9.4中合并json1和json2

WITH all_json_key_value AS (
  SELECT id, t1.key, t1.value FROM test, jsonb_each(json1) as t1
  UNION
  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 
GROUP BY id

编辑 ：对于PostgreSQL 9.5+，请看下面的Zubin的答案