我无法找出最好的方式来表示连接的行作为嵌套对象(1：1关系)
-- 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,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$$;
查询本身：
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;
我发现如果我使用ROW()，我可以将结果字段分成一个子对象，但似乎限于一个单一的级别。我不能插入更多的AS XXX语句，因为我认为我应该需要在这种情况下。
我得到列名，因为我转换到适当的记录类型，例如与:: user_roles，在该表的结果的情况下。
{
"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"
}
}
}
}
我想做的是为连接生成JSON(再次1：1很好)，我可以添加连接，并将它们表示为他们加入的父对象的子对象，即如下所示：
{
"id":1,"user_role":{
"id":1,"duty_id":1
"duty":{
"id":1,"name":"Script Execution"
}
}
}
}
任何帮助是赞赏。谢谢阅读。
to_json,json_build_object,json_object and 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,ur.name,'description',ur.description,'duty_id',ur.duty_id,'duty',json_build_object(
'id',d.id,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,duty_id,duty) on urd.id = u.user_role_id
) row;
产生，通过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"
}
}
}
当你有一个1：many关系，btw时，你将需要使用array_to_json(array_agg(…))。
上述查询应该理想地能够写成：
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列别名。可悲的是。
幸运的是，他们优化了相同。比较计划：
> nested subquery version; vs
>后面的nested ROW constructor version删除了别名，所以它执行
因为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,c) AS x(name1,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}]没有像文本那样再次转义。
这意味着你可以组合json操作来构造行，你不必总是创建非常复杂的Postgresql复合类型，然后在输出上调用row_to_json。
