formato - Fusión de columnas JSON(B) de concatenación en la consulta

sharepoint formato condicional

Question

Al usar Postgres 9.4, estoy buscando una manera de combinar dos (o más) columnas json o jsonb en una consulta. Considere la siguiente tabla como ejemplo:

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

¿Es posible que la consulta devuelva lo siguiente?

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

Desafortunadamente, no puedo definir una función como se describe here . ¿Es esto posible con una consulta "tradicional"?

Answer 1

Aquí está la lista completa de funciones integradas que se pueden usar para crear objetos json en PostgreSQL. http://www.postgresql.org/docs/9.4/static/functions-json.html

• row_to_json y json_object no le permiten definir sus propias claves, por lo que no se pueden usar aquí
• json_build_object espera que sepa de antemano cuántas claves y valores tendrá nuestro objeto, ese es el caso en su ejemplo, pero no debería ser el caso en el mundo real
• json_object parece una buena herramienta para abordar este problema, pero nos obliga a convertir nuestros valores en texto para que no podamos usar este tampoco.

Bueno ... ok, wo no podemos usar ninguna función clásica.

Echemos un vistazo a algunas funciones agregadas y esperemos lo mejor ... http://www.postgresql.org/docs/9.4/static/functions-aggregate.html

json_object_agg es la única función agregada que construye objetos, es nuestra única oportunidad de abordar este problema. El truco aquí es encontrar la forma correcta de alimentar la función json_object_agg .

Aquí está mi tabla de prueba y datos

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"}}'');

Y después de algunas pruebas y errores con json_object aquí hay una consulta que puede usar para combinar json1 y json2 en 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

EDITAR : para PostgreSQL 9.5+, mira la respuesta de Zubin a continuación

Answer 2

En Postgres 9.5+ puedes combinar JSONB así:

select json1 || json2;

O, si es JSON, coaccione a JSONB si es necesario:

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

O:

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

(De lo contrario, cualquier valor nulo en json1 o json2 devuelve una fila vacía)

Por ejemplo:

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

Felicitaciones a @MattZukowski por señalar esto en un comentario.

Answer 3

Esta función fusionaría objetos json anidados.

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$;

Answer 4

Para su información, si alguien usa jsonb en> = 9.5 y solo les importa que los elementos de nivel superior se fusionen sin claves duplicadas, es tan fácil como usar el || operador:

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;
$$;

Answer 5

Sin embargo, esta pregunta ya está respondida hace algún tiempo; el hecho de que cuando json1 y json2 contienen la misma clave; La clave aparece dos veces en el documento, no parece ser la mejor práctica .

Por lo tanto, puede usar esta función jsonb_merge con PostgreSQL 9.5:

select id, jsonb_merge(json1, json2) from test

La siguiente consulta devuelve las columnas jsonb concatenadas, donde las claves en json2 son dominantes sobre las claves en json1 :

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

Answer 6

También puede transformar json en texto, concatenar, reemplazar y volver a convertir a json. Usando los mismos datos de Clément puedes hacer:

SELECT regexp_replace(
    array_agg((json1))::text,
    ''}"(,)"{|//| |^{"|"}$'', 
    ''/1'', 
    ''g''
)::json
FROM test

También puedes concatenar todo json1 en json único con:

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;

Answer 7

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$;

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