PostgreSQL 9.2 row_to_json()与嵌套连接

前端之家收集整理的这篇文章主要介绍了PostgreSQL 9.2 row_to_json()与嵌套连接前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
我试图使用在Postgresql 9.2中添加的row_to_json()函数查询的结果映射到JSON。

我无法找出最好的方式来表示连接的行作为嵌套对象(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"
         }
      }
   }
}

任何帮助是赞赏。谢谢阅读。

更新:在Postgresql 9.4这提高了很多 with the introduction of 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。

原文链接:https://www.f2er.com/postgresql/193740.html

猜你在找的Postgre SQL相关文章