PostgreSQL 9.2 row_to_json () avec jointures imbriquées

Question

Je suis en train de cartographier les résultats d'une requête JSON à l'aide de l' row_to_json() fonction qui a été ajoutée dans PostgreSQL 9.2.

Je vais avoir de la difficulté à déterminer la meilleure manière de représenter des lignes jointes comme des objets imbriqués (1:1 relations)

Voici ce que j'ai essayé (code d'installation: les tableaux, les données de l'échantillon, suivie par la 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 découvert que si j'ai utilisé ROW(), je pourrais séparer les champs qui en résultent dans un objet enfant, mais il semble limitée à un seul niveau. Je ne peux pas insérer plus d' AS XXX des déclarations, je pense que j'en aurais besoin dans ce cas.

Je suis offerte les noms de colonne, parce que je l'ai jeté à la appropriée de type d'enregistrement, par exemple avec ::user_roles, dans le cas de la table de résultats.

Voici ce que la 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 est de générer du JSON pour les jointures (encore 1:1 est très bien) dans une voie où je peux ajouter des jointures, et les ont représentés comme des objets enfants de parents ils rejoignent, c'est à dire comme ce qui suit:

{
   "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 pour la lecture.

Answer 1

Mise à jour: Dans PostgreSQL 9.4 cela améliore beaucoup avec l'introduction de l' to_json, json_build_object, json_object et json_build_array. Pour les versions plus anciennes, lire sur.

---

Il n'est pas limité à une seule ligne, c'est juste un peu douloureux. Vous ne pouvez pas alias composite rowtypes l'aide d' AS, de sorte que vous besoin d'utiliser un alias sous-requête de l'expression ou de la CTE, pour obtenir l'effet:

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 souhaitez utiliser array_to_json(array_agg(...)) quand vous avez un 1:de nombreuses relations, btw.

La requête ci-dessus devrait idéalement être en mesure d'être écrite comme:

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 PostgreSQL est ROW constructeur n'accepte pas l' AS des alias de colonne. Malheureusement.

Heureusement, ils permettent d'optimiser le même. Comparer les plans:

• La sous-requête imbriquée version; vs
• Le dernier imbriquée ROW constructeur version avec les alias retiré, afin qu'il s'exécute

Parce que les expressions de table communes sont l'optimisation de clôtures, de modifier le libellé du sous-requête imbriquée version à utiliser enchaîné d'expressions de table communes (WITH expressions) ne fonctionne pas bien, et ne se traduira pas dans le même plan. Dans ce cas, elles sont coincées avec des moches les sous-requêtes imbriquées jusqu'à ce que nous obtenir quelques améliorations row_to_json ou un moyen de remplacer les noms de colonne dans un ROW constructeur plus directement.

---

De toute façon, en général, le principe est que l'endroit où vous souhaitez créer un objet json avec des colonnes a, b, c, et vous souhaitez, vous pouvez 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 de retour de la ligne des valeurs typées:

(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

En outre, gardez à l'esprit que vous pouvez composer json valeurs sans supplémentaires de citer, par exemple, si vous mettez de la sortie d'un json_agg dans un row_to_json, à l'intérieur json_agg résultat n'obtiendrez pas cité comme un string, ça va être intégrés directement en json.

par exemple, dans l'exemple au hasard:

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
);

la sortie est:

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

Notez que l' json_agg de produits, [{"a":1,"b":2}, {"a":1,"b":2}], n'a pas échappé à nouveau, text serait.

Cela signifie que vous pouvez composer json opérations de construction de lignes, vous n'avez toujours pas de créer extrêmement complexe PostgreSQL types de composé puis appelez row_to_json sur la sortie.