PostgreSQL 9.2 row_to_json () avec des jointures nestedes

J’essaie de mapper les résultats d’une requête sur JSON en utilisant la fonction row_to_json() ajoutée dans PostgreSQL 9.2.

J’ai du mal à trouver la meilleure façon de représenter les lignes jointes en tant qu’objects nesteds (relations 1: 1)

Voici ce que j’ai essayé (code de configuration: tableaux, exemples de données, suivi d’une requête):

 -- some test tables to start out with: create table role_duties ( id serial primary key, name varchar ); create table user_roles ( id serial primary key, name varchar, description varchar, duty_id int, foreign key (duty_id) references role_duties(id) ); create table users ( id serial primary key, name varchar, email varchar, user_role_id int, foreign key (user_role_id) references user_roles(id) ); DO $$ DECLARE duty_id int; DECLARE role_id int; begin insert into role_duties (name) values ('Script Execution') returning id into duty_id; insert into user_roles (name, description, duty_id) values ('admin', 'Administrative duties in the system', duty_id) returning id into role_id; insert into users (name, email, user_role_id) values ('Dan', 'someemail@gmail.com', role_id); END$$; 

La requête elle-même:

 select row_to_json(row) from ( select u.*, ROW(ur.*::user_roles, ROW(d.*::role_duties)) as user_role from users u inner join user_roles ur on ur.id = u.user_role_id inner join role_duties d on d.id = ur.duty_id ) row; 

J’ai trouvé que si j’utilisais ROW() , je pourrais séparer les champs résultants en un object enfant, mais cela semble limité à un seul niveau. Je ne peux pas insérer plus de déclarations AS XXX , comme je pense que je devrais en avoir besoin dans ce cas.

Les noms de colonnes me sont accordés, car je lance le type d’enregistrement approprié, par exemple avec ::user_roles , dans le cas des résultats de cette table.

Voici ce que cette requête renvoie:

 { "id":1, "name":"Dan", "email":"someemail@gmail.com", "user_role_id":1, "user_role":{ "f1":{ "id":1, "name":"admin", "description":"Administrative duties in the system", "duty_id":1 }, "f2":{ "f1":{ "id":1, "name":"Script Execution" } } } } 

Ce que je veux faire, c’est générer JSON pour les jointures (là encore, 1: 1 convient), de manière à pouvoir append des jointures et les représenter comme des objects enfants des parents auxquels ils se joignent, à savoir:

 { "id":1, "name":"Dan", "email":"someemail@gmail.com", "user_role_id":1, "user_role":{ "id":1, "name":"admin", "description":"Administrative duties in the system", "duty_id":1 "duty":{ "id":1, "name":"Script Execution" } } } } 

Toute aide est appréciée. Merci d’avoir lu.

Mise à jour: Dans PostgreSQL 9.4, cela améliore beaucoup avec l’introduction de to_json , json_build_object , json_object et json_build_array , bien qu’il soit verbeux en raison de la nécessité de nommer explicitement tous les champs:

 select json_build_object( 'id', u.id, 'name', u.name, 'email', u.email, 'user_role_id', u.user_role_id, 'user_role', json_build_object( 'id', ur.id, 'name', ur.name, 'description', ur.description, 'duty_id', ur.duty_id, 'duty', json_build_object( 'id', d.id, 'name', d.name ) ) ) from users u inner join user_roles ur on ur.id = u.user_role_id inner join role_duties d on d.id = ur.duty_id; 

Pour les anciennes versions, lisez la suite.


Ce n’est pas limité à une seule ligne, c’est juste un peu pénible. Vous ne pouvez pas utiliser un pseudonyme pour composer des types de ligne composites, vous devez donc utiliser une expression de sous-requête avec alias ou CTE pour obtenir l’effet suivant:

 select row_to_json(row) from ( select u.*, urd AS user_role from users u inner join ( select ur.*, d from user_roles ur inner join role_duties d on d.id = ur.duty_id ) urd(id,name,description,duty_id,duty) on urd.id = u.user_role_id ) row; 

produit, via http://jsonprettyprint.com/ :

 { "id": 1, "name": "Dan", "email": "someemail@gmail.com", "user_role_id": 1, "user_role": { "id": 1, "name": "admin", "description": "Administrative duties in the system", "duty_id": 1, "duty": { "id": 1, "name": "Script Execution" } } } 

Vous voudrez utiliser array_to_json(array_agg(...)) lorsque vous avez une relation 1: many, btw.

La requête ci-dessus devrait idéalement pouvoir être écrite comme suit:

 select row_to_json( ROW(u.*, ROW(ur.*, d AS duty) AS user_role) ) from users u inner join user_roles ur on ur.id = u.user_role_id inner join role_duties d on d.id = ur.duty_id; 

… mais le constructeur ROW de PostgreSQL n’accepte pas les alias de colonne AS . Malheureusement.

Heureusement, ils optimisent les mêmes. Comparez les plans:

  • La version de la sous-requête nestede ; contre
  • La dernière version du constructeur ROW nested avec les alias supprimés de sorte qu’il s’exécute

Étant donné que les CTE sont des barrières d’optimisation, la reformulation de la version de sous-requête nestede pour utiliser des CTE chaînés (expressions WITH ) peut ne pas être aussi efficace et ne doit pas aboutir au même plan. Dans ce cas, vous êtes en quelque sorte coincé avec des sous-requêtes nestedes jusqu’à ce que nous obtenions des améliorations à row_to_json ou un moyen de remplacer plus directement les noms de colonnes dans un constructeur ROW .


En tout cas, en principe, le principe est que lorsque vous voulez créer un object json avec les colonnes a, b, c et que vous souhaitez simplement écrire la syntaxe illégale:

 ROW(a, b, c) AS outername(name1, name2, name3) 

vous pouvez à la place utiliser des sous-requêtes scalaires renvoyant des valeurs de type ligne:

 (SELECT x FROM (SELECT a AS name1, b AS name2, c AS name3) x) AS outername 

Ou:

 (SELECT x FROM (SELECT a, b, c) AS x(name1, name2, name3)) AS outername 

De plus, gardez à l’esprit que vous pouvez composer des valeurs json sans autres guillemets, par exemple si vous placez un json_agg dans un row_to_json , le résultat interne json_agg ne sera pas cité en tant que chaîne, il sera directement incorporé en tant que json.

par exemple dans l’exemple arbitraire:

 SELECT row_to_json( (SELECT x FROM (SELECT 1 AS k1, 2 AS k2, (SELECT json_agg( (SELECT x FROM (SELECT 1 AS a, 2 AS b) x) ) FROM generate_series(1,2) ) AS k3 ) x), true ); 

le résultat est:

 {"k1":1, "k2":2, "k3":[{"a":1,"b":2}, {"a":1,"b":2}]} 

Notez que le produit json_agg , [{"a":1,"b":2}, {"a":1,"b":2}] , n’a pas encore été échappé, comme le serait le text .

Cela signifie que vous pouvez composer des opérations json pour construire des lignes, vous ne devez pas toujours créer des types composites PostgreSQL extrêmement complexes, puis appeler row_to_json sur la sortie.

Ma suggestion pour la maintenabilité à long terme est d’utiliser une vue pour construire la version grossière de votre requête, puis utiliser une fonction comme ci-dessous:

 CREATE OR REPLACE FUNCTION fnc_query_prominence_users( ) RETURNS json AS $$ DECLARE d_result json; BEGIN SELECT ARRAY_TO_JSON( ARRAY_AGG( ROW_TO_JSON( CAST(ROW(users.*) AS prominence.users) ) ) ) INTO d_result FROM prominence.users; RETURN d_result; END; $$ LANGUAGE plpgsql SECURITY INVOKER; 

Dans ce cas, l’object prominence.users est une vue. Depuis que j’ai sélectionné des utilisateurs. *, Je n’aurai pas à mettre à jour cette fonction si je dois mettre à jour la vue pour inclure davantage de champs dans un enregistrement d’utilisateur.