Ответ 1
Обновление: в 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: много, btw.
Вышеприведенный запрос в идеале должен быть написан как:
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. К сожалению.
К счастью, они оптимизируют то же самое. Сравните планы:
- вложенная версия подзапроса; против
- Последняя вложенная версия
ROWконструктор с удаленными псевдонимами, поэтому она выполняет
Поскольку CTE являются оптимизационными заборами, перефразировать вложенную версию подзапроса для использования целых CTE (выражений 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 значения без дополнительного цитирования, например. если вы поместите вывод 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 на выходе.