PostgreSQL 9.2 row_to_json() avec imbriqué rejoint

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, si c'est prolixe en raison de la nécessité de nommer tous les domaines explicitement:

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 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 de AS, si vous avez 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": "[email protected]",
  "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(...)) lorsque 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 AS les 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 LIGNE constructeur de 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)

place, vous pouvez 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'arbitraire exemple:

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}]}

Noter que le json_agg produit, [{"a":1,"b":2}, {"a":1,"b":2}], n'a pas échappé à nouveau, comme 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é alors appel row_to_json sur la sortie.