PostgreSQL的9.2 row_to_json（）嵌套加入(PostgreSQL 9.2 ro

我试图映射一个查询结果使用到JSON row_to_json()这是在PostgreSQL的9.2增加的功能。

我有麻烦找出代表参加行作为嵌套对象的最佳方式（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,
    name varchar,
    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,
   "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"
         }
      }
   }
}

任何帮助表示赞赏。谢谢阅读。

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

对于旧版本，请继续阅读。

它不局限于单个行，它只是有点痛苦。你可以不使用别名复合rowtypes 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": "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"
    }
  }
}

您将要使用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列别名。可悲的是。

值得庆幸的是，他们优化了一样。比较计划：

该嵌套子查询的版本 ; VS
后者嵌套ROW构造函数的版本移除了别名，以便其执行

由于热膨胀系数是优化围栏，改写嵌套子查询的版本使用链式的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的输出。

Answer 2:

我对可维护性的长期的建议是使用视图来构建查询的粗糙的版本，然后用功能如下：

CREATE OR REPLACE FUNCTION fnc_query_prominence_users( )
RETURNS json AS $$
DECLARE
    d_result            json;
BEGIN
    SELECT      ARRAY_TO_JSON(
                    ARRAY_AGG(
                        ROW_TO_JSON(
                            CAST(ROW(users.*) AS prominence.users)
                        )
                    )
                )
        INTO    d_result
        FROM    prominence.users;
    RETURN d_result;
END; $$
LANGUAGE plpgsql
SECURITY INVOKER;

在这种情况下，对象prominence.users是一个视图。由于我选择的用户。*，我不会有更新此功能，如果我需要更新视图，以包含在用户记录更多的领域。

文章来源: PostgreSQL 9.2 row_to_json() with nested joins