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