带有嵌套联接的PostgreSQL 9.2 row_to_json（）

Question

我正在尝试使用row_to_json()PostgreSQL 9.2中添加的功能将查询结果映射到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,
    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', '[e-mail]', 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":"[e-mail]",
   "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":"[e-mail]",
   "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;

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

它不限于单行，只是有点痛苦。您不能使用来别名复合行类型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": "[e-mail]",
  "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(...))一对一的关系，您将要使用，顺便说一句。

理想情况下，上面的查询应该可以编写为：

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更直接地覆盖构造函数中的列名的方法为止。

总之，总的来说，该原则是，您想在其中创建一个带有column的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输出。