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"?

26
demandé sur Erwin Brandstetter 2015-05-07 15:51:07

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 et json_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

22
répondu Clément Prévost 2017-10-05 09:58:56

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.

33
répondu Zubin 2017-05-18 03:15:26

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
5
répondu caiohamamura 2016-03-10 14:37:05

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
4
répondu API 2017-05-23 12:26:21

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)
3
répondu Arthur Nascimento 2017-04-17 19:15:52
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

0
répondu igilfanov 2016-05-20 10:07:46

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$;
0
répondu Sandeep Sinha 2018-04-06 08:28:45