PostgreSQL 9.2 строка в json () с вложенными соединениями

обновление: в PostgreSQL 9.4 это значительно улучшает введение to_json,json_build_object,json_object и json_build_array, хотя это многословно из-за необходимости явно называть все поля:

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;

для более старых версий, Читать далее.

---

это не ограничивается одной строкой, это просто немного больно. Вы не можете псевдоним композитных типов строк с помощью AS, поэтому вам нужно использовать псевдонимное выражение подзапроса или CTE для достижения эффект:

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;

производит, через 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"
    }
  }
}

вы хотите использовать array_to_json(array_agg(...)) когда у вас есть 1:многим, кстати.

приведенный выше запрос в идеале должен быть написан как:

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;

... но в PostgreSQL ROW конструктор не принимает AS псевдонимы столбцов. Грустно.

к счастью, они оптимизируют то же самое. Сравните планы:

• The вложенный подзапрос версия; vs
• последний вложенные ROW конструктора версии с удаленными псевдонимами, поэтому он выполняет

потому что Ассоциация является оптимизация заборы, перефразируя вложенный подзапрос версия для использования прикован ОТВ (WITH выражения) может не работать, а также, и не приведет к тому же плану. В этом случае вы как бы застряли с уродливыми вложенными подзапросами, пока мы не получим некоторые улучшения в row_to_json или способ переопределения имен столбцов в ROW конструктор более непосредственно.

---

в любом случае, в общем, принцип заключается в том, что там, где вы хотите создать объект json со столбцами a, b, c, и вы хотите, чтобы вы могли просто написать незаконный синтаксис:

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

вместо этого вы можете использовать скалярные подзапросы, возвращающие значения типа строки:

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

или:

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

кроме того, имейте в виду, что вы можете сочинить json значения без дополнительной кавычки, например, если вы ставите вывод a json_agg внутри row_to_json внутренний json_agg результат не будет цитироваться как строка, он будет включен непосредственно как json.

например, в качестве примера:

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

выход:

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

отметим, что json_agg продукт [{"a":1,"b":2}, {"a":1,"b":2}], не сбежал снова, как text будет.

это означает, что вы можете написать в формате JSON операции для построения строк вам не всегда нужно создавать чрезвычайно сложные составные типы PostgreSQL, а затем вызывать row_to_json на выходе.