合并连接查询中的JSON(B)列

使用Postgres 9.4,我正在寻找一种在查询中合并两个(或更多) jsonjsonb列的方法。 以下面的表格为例:

  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'}}

不幸的是,我不能像这里描述的那样定义一个函数。 这是可能的“传统”查询?


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

  • row_to_jsonjson_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的答案


    在Postgres 9.5+中,你可以像这样合并JSONB:

    select json1 || json2;
    

    或者,如果它是JSON,必要时强制JSONB:

    select json1::jsonb || json2::jsonb;
    

    例如:

    select data || '{"foo":"bar"}'::jsonb from photos limit 1;
                                   ?column?
    ----------------------------------------------------------------------
     {"foo": "bar", "preview_url": "https://unsplash.it/500/720/123"}
    

    荣誉@MattZukowski在评论中指出了这一点。


    您也可以将json转换为文本,连接,替换并转换回json。 使用来自Clément的相同数据,您可以:

    SELECT replace(
        (json1::text || json2::text), 
        '}{', 
        ', ')::json 
    FROM test
    

    您还可以将所有json1连接到单个json中:

    SELECT regexp_replace(
        array_agg((json1))::text,
        '}"(,)"{|| |^{"|"}$', 
        '1', 
        'g'
    )::json
    FROM test
    
    链接地址: http://www.djcxy.com/p/37829.html

    上一篇: Merging Concatenating JSON(B) columns in query

    下一篇: Return Json object with Duplicate Keys using C#