Convertir une table clé / valeur aplatie en JSON hiérarchique dans PostgreSQL

Je ne peux pas vraiment penser à quelque chose de plus simple, même si je pense qu'il devrait y avoir un moyen plus simple.

Je suppose qu'il y a une colonne supplémentaire qui peut être utilisée pour rassembler les clés appartenant à une "personne", J'ai utilisé p_id pour cela dans mon exemple.

p_id | jsonb_object_agg                                                      
-----+-----------------------------------------------------------------------
   1 | {"name": "Bob", "address": {"city": "Vancouver", "country": "Canada"}}
   2 | {"name": "John", "address": {"city": "Munich", "country": "Germany"}} 

La requête la plus interne convertit simplement la notation par points en un tableau pour un accès plus facile plus tard.

Le niveau suivant construit ensuite des objets JSON en fonction de la "clé". Pour les clés "à un seul niveau", il utilise juste la clé / valeur, pour les autres, il utilise le deuxième élément + la valeur et agrège ensuite ceux qui appartiennent ensemble.

Le deuxième niveau de requête renvoie ce qui suit:

create table kv (p_id integer, "key" text, value text);
insert into kv
values
(1, 'name','Bob'),
(1, 'address.city','Vancouver'),
(1, 'address.country','Canada'),
(2, 'name','John'),
(2, 'address.city','Munich'),
(2, 'address.country','Germany');

L'agrégation effectuée à la deuxième étape, laisse un niveau de trop pour les clés "élément unique" , et c'est pour cela que nous avons besoin de niveau.

Si cette distinction n'était pas faite, l'agrégation finale renverrait {"name": {"name": "Bob"}, "address": {"city": "Vancouver" , "country": "Canada"}} au lieu du recherché: {"name": "Bob", "address": {"city": "Vancouver", "country": "Canada "}} .

L'expression case level when 1 then v -> k else v end tourne essentiellement {" name ":" Bob "} retour à "Bob".

Donc, avec les exemples de données suivants:

p_id | k       | v                                          | level
-----+---------+--------------------------------------------+------
   1 | address | {"city": "Vancouver", "country": "Canada"} |     2
   1 | name    | {"name": "Bob"}                            |     1
   2 | address | {"city": "Munich", "country": "Germany"}   |     2
   2 | name    | {"name": "John"}                           |     1

puis la requête renvoie:

select p_id, 
       jsonb_object_agg(k, case level when 1 then v -> k else v end) 
from (
  select p_id, 
         elements[1] k, 
         jsonb_object_agg(case cardinality(elements) when 1 then ky else elements[2] end, value) v, 
         max(cardinality(elements)) as level
  from (       
    select p_id, 
           "key" as ky, 
           string_to_array("key", '.') as elements, value
    from kv 
  ) t1
  group by p_id, k
) t2
group by p_id;

Exemple en ligne: https: // rextester. com / SJOTCD7977

select json_agg(json_build_object('name',name, 'address', json_build_object('city',address_city, 'country', address_country)))
    from  v_datos;

Il n'y a pas d'outils prêts à l'emploi pour cela. La fonction génère un objet json hiérarchique basé sur un chemin:

{
    "name": "Bob",
    "first":
    {
        "second":
        {
            "third": "value"
        }
    },
    "address":
    {
        "city": "Vancouver",
        "country": "Canada"
    }
}

Vous avez également besoin de la fonction d'agrégation jsonb_merge_agg (jsonb) décrite dans cette réponse. La requête:

with my_table (path, value) as (
values
    ('name', 'Bob'),
    ('address.city', 'Vancouver'),
    ('address.country', 'Canada'),
    ('first.second.third', 'value')
)

select jsonb_merge_agg(jsonb_build_object_from_path(path, value))
from my_table;

donne cet objet:

create or replace function jsonb_build_object_from_path(path text, value text)
returns jsonb language plpgsql as $$
declare
    obj jsonb;
    keys text[] := string_to_array(path, '.');
    level int := cardinality(keys);
begin
    obj := jsonb_build_object(keys[level], value);
    while level > 1 loop
        level := level- 1;
        obj := jsonb_build_object(keys[level], obj);
    end loop;
    return obj;
end $$;

jsonb_set () presque fait tout pour vous, mais malheureusement, il ne peut créer que des feuilles manquantes (c'est-à-dire les dernières clés manquantes sur un chemin), mais pas des branches manquantes entières. Pour surmonter cela, voici une version modifiée de celui-ci, qui peut définir des valeurs sur tous les niveaux manquants:

select   grp, jsonb_set_agg(to_jsonb(value), string_to_array(key, '.'))
from     eav_tbl
group by grp;

Il ne vous reste plus qu'à appliquer cette fonction une par une à vos lignes, en commençant par avec un objet json vide: {} . Vous pouvez le faire avec les CTE récursifs :

XXX

Ou , avec un agrégat personnalisé défini comme:

create aggregate jsonb_set_agg(jsonb, text[]) (
  sfunc    = jsonb_set_rec,
  stype    = jsonb,
  initcond = '{}'
);

votre requête pourrait devenir aussi simple que:

with recursive props as (
   (select   distinct on (grp)
             pk, grp, jsonb_set_rec('{}', to_jsonb(value), string_to_array(key, '.')) json_object
    from     eav_tbl
    order by grp, pk)
  union all
   (select   distinct on (grp)
             eav_tbl.pk, grp, jsonb_set_rec(json_object, to_jsonb(value), string_to_array(key, '.'))
    from     props
    join     eav_tbl using (grp)
    where    eav_tbl.pk > props.pk
    order by grp, eav_tbl.pk)
)
select   distinct on (grp)
         grp, json_object
from     props
order by grp, pk desc;

https://rextester.com/TULNU73750