Объединение конкатенаций столбцов JSON (B) в запросе

Ответ 1

Вот полный список встроенных функций, которые можно использовать для создания объектов json в PostgreSQL. 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 вот запрос, который вы можете использовать для объединения json1 и json2 в PostgreSQL 9.4

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 ниже

Ответ 2

В Postgres 9. 5+ вы можете объединить JSONB следующим образом:

select json1 || json2;

Или, если это JSON, приведите к JSONB, если необходимо:

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

Или же:

select COALESCE(json1::jsonb||json2::jsonb, json1::jsonb, json2::jsonb);

(В противном случае любое нулевое значение в json1 или json2 возвращает пустую строку)

Например:

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

Престижность @MattZukowski за указание на это в комментарии.

Ответ 3

Также вы можете преобразовать 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

Ответ 4

Однако на этот вопрос ответили уже некоторое время назад; тот факт, что когда json1 и json2 содержат один и тот же ключ; ключ дважды появляется в документе, не представляется лучшей практикой.

Поэтому u может использовать эту функцию jsonb_merge с PostgreSQL 9.5:

CREATE OR REPLACE FUNCTION jsonb_merge(jsonb1 JSONB, jsonb2 JSONB)
    RETURNS JSONB AS $$
    DECLARE
      result JSONB;
      v RECORD;
    BEGIN
       result = (
    SELECT json_object_agg(KEY,value)
    FROM
      (SELECT jsonb_object_keys(jsonb1) AS KEY,
              1::int AS jsb,
              jsonb1 -> jsonb_object_keys(jsonb1) AS value
       UNION SELECT jsonb_object_keys(jsonb2) AS KEY,
                    2::int AS jsb,
                    jsonb2 -> jsonb_object_keys(jsonb2) AS value ) AS t1
           );
       RETURN result;
    END;
    $$ LANGUAGE plpgsql;

Следующий запрос возвращает конкатенированные столбцы jsonb, где ключи в json2 являются доминирующими над ключами в json1:

select id, jsonb_merge(json1, json2) from test

Ответ 5

FYI, если кто-то использует jsonb в >= 9.5, и им все равно, что элементы верхнего уровня сливаются без дубликатов ключей, тогда это так же просто, как использование || Оператор:

select '{"a1": "b2"}'::jsonb || '{"f":{"g" : "h"}}'::jsonb;
      ?column?           
-----------------------------
 {"a1": "b2", "f": {"g": "h"}}
(1 row)

Ответ 6

Эта функция будет объединять вложенные объекты JSON

create or replace function jsonb_merge(CurrentData jsonb,newData jsonb)
 returns jsonb
 language sql
 immutable
as $jsonb_merge_func$
 select case jsonb_typeof(CurrentData)
   when 'object' then case jsonb_typeof(newData)
     when 'object' then (
       select    jsonb_object_agg(k, case
                   when e2.v is null then e1.v
                   when e1.v is null then e2.v
                   when e1.v = e2.v then e1.v 
                   else jsonb_merge(e1.v, e2.v)
                 end)
       from      jsonb_each(CurrentData) e1(k, v)
       full join jsonb_each(newData) e2(k, v) using (k)
     )
     else newData
   end
   when 'array' then CurrentData || newData
   else newData
 end
$jsonb_merge_func$;

Ответ 7

CREATE OR REPLACE FUNCTION jsonb_merge(pCurrentData jsonb, pMergeData jsonb, pExcludeKeys text[])
RETURNS jsonb IMMUTABLE LANGUAGE sql
AS $$
    SELECT json_object_agg(key,value)::jsonb
    FROM (
        WITH to_merge AS (
            SELECT * FROM jsonb_each(pMergeData) 
        )
        SELECT *
        FROM jsonb_each(pCurrentData)
        WHERE key NOT IN (SELECT key FROM to_merge)
     AND ( pExcludeKeys ISNULL OR key <> ALL(pExcludeKeys))
        UNION ALL
        SELECT * FROM to_merge
    ) t;
$$;

SELECT jsonb_merge ('{ "a": 1, "b": 9, "c": 3, "e": 5}':: jsonb, '{ "b": 2, "d": 4 } ':: jsonb,' { "c", "e" } ':: text []) как jsonb

Ответ 8

Попробуйте это, если у кого-то есть проблема слияния двух объектов JSON

select table.attributes::jsonb || json_build_object('foo',1,'bar',2)::jsonb FROM table where table.x='y';

Ответ 9

хорошо работает как альтернатива || когда требуется рекурсивное глубокое слияние (найдено здесь):

create or replace function jsonb_merge_recurse(orig jsonb, delta jsonb)
returns jsonb language sql as $$
    select
        jsonb_object_agg(
            coalesce(keyOrig, keyDelta),
            case
                when valOrig isnull then valDelta
                when valDelta isnull then valOrig
                when (jsonb_typeof(valOrig) <> 'object' or jsonb_typeof(valDelta) <> 'object') then valDelta
                else jsonb_merge_recurse(valOrig, valDelta)
            end
        )
    from jsonb_each(orig) e1(keyOrig, valOrig)
    full join jsonb_each(delta) e2(keyDelta, valDelta) on keyOrig = keyDelta
$$;