Fusion des colonnes Concaténantes JSON(B) dans la requête
en utilisant Postgres 9.4, je cherche un moyen de fusionner deux (ou plus) colonnes json
ou jsonb
dans une requête. Considérons le tableau suivant comme exemple:
id | json1 | json2
----------------------------------------
1 | {'a':'b'} | {'c':'d'}
2 | {'a1':'b2'} | {'f':{'g' : 'h'}}
est - il possible que la requête renvoie ce qui suit:
id | json
----------------------------------------
1 | {'a':'b', 'c':'d'}
2 | {'a1':'b2', 'f':{'g' : 'h'}}
malheureusement, je ne peux pas définir une fonction comme décrit ici . Est-ce possible avec une requête "traditionnelle"?
7 réponses
Voici la liste complète des fonctions qui peuvent être utilisées pour créer des objets json dans PostgreSQL. http://www.postgresql.org/docs/9.4/static/functions-json.html
-
row_to_json
etjson_object
ne vous permettent pas de définir vos propres clés, de sorte qu'il ne peut pas être utilisé ici -
json_build_object
attendez vous à savoir par avance combien de clés et de valeurs de notre objet, c'est le cas dans votre exemple, mais ne devrait pas être le cas dans le monde réel -
json_object
semble être un bon outil pour s'attaquer à ce problème, mais il nous force à jeter nos valeurs au texte afin que nous ne pouvons pas utiliser celui-ci non plus
bien... on ne peut pas utiliser de fonctions classiques.
jetons un coup d'oeil à quelques fonctions agrégées et espérons pour le meilleur... http://www.postgresql.org/docs/9.4/static/functions-aggregate.html
json_object_agg
est la seule fonction agrégée qui construit des objets, c'est notre seule chance de s'attaquer à ce problème. Le truc ici est de trouver la bonne façon d'alimenter la fonction json_object_agg
.
voici ma table de test et mes données
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"}}');
et après quelques essais et erreurs avec json_object
voici une requête que vous pouvez utiliser pour fusionner json1 et json2 dans 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
EDIT : pour PostgreSQL 9.5+, voir la réponse de Zubin ci-dessous
dans Postgres 9.5+ vous pouvez fusionner JSONB comme ceci:
select json1 || json2;
Ou, si c'est du JSON, la contraindre à JSONB si nécessaire:
select json1::jsonb || json2::jsonb;
par exemple:
select data || '{"foo":"bar"}'::jsonb from photos limit 1;
?column?
----------------------------------------------------------------------
{"foo": "bar", "preview_url": "https://unsplash.it/500/720/123"}
félicitations à @MattZukowski pour avoir souligné cela dans un commentaire.
vous pouvez aussi transformer json en texte, concaténer, remplacer et convertir de nouveau en json. En utilisant les mêmes données de Clément vous pouvez faire:
SELECT replace(
(json1::text || json2::text),
'}{',
', ')::json
FROM test
vous pouvez aussi concaténer tous les json1 en un seul json avec:
SELECT regexp_replace(
array_agg((json1))::text,
'}"(,)"{|\| |^{"|"}$',
'',
'g'
)::json
FROM test
cependant cette question est déjà répondue il y a quelque temps; le fait que lorsque json1
et json2
contiennent la même clé; la clé apparaît deux fois dans le document, ne semble pas être meilleure pratique .
donc vous pouvez utiliser cette fonction jsonb_merge
avec 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;
la requête suivante renvoie les colonnes concaténées jsonb, où les touches dans json2
sont dominantes sur le touches json1
:
select id, jsonb_merge(json1, json2) from test
POUR INFO, si quelqu'un utilise jsonb dans >= 9.5 et qu'il ne se soucie que des éléments de haut niveau fusionnés sans clés dupliquées, alors c'est aussi simple que d'utiliser l'opérateur||:
select '{"a1": "b2"}'::jsonb || '{"f":{"g" : "h"}}'::jsonb;
?column?
-----------------------------
{"a1": "b2", "f": {"g": "h"}}
(1 row)
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;
$$;
SÉLECTIONNEZ jsonb_merge('{"a": 1, "b": 9, "c": 3, "e":5}'::jsonb, '{"b": 2, "d": 4}'::jsonb, '{"c","e"}'::text[]) jsonb
cette fonction fusionnerait les objets JSON imbriqués
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$;