postgresql - 使用带有嵌套连接的 row_to_json()

Question

我正在尝试使用 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,
    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 中，这改进了很多 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,
                        '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": "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(...))当你有一对多的关系时，顺便说一句。

上述查询理想情况下应该可以写成:

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 ;对比
• 后者nested ROW constructor version删除别名以便执行

由于 CTE 是优化围栏，因此将嵌套子查询版本改写为使用链式 CTE(WITH 表达式)可能效果不佳，并且不会产生相同的计划。在这种情况下，在我们对 row_to_json 进行一些改进之前，您会被难看的嵌套子查询所困扰。或覆盖 ROW 中的列名的方法构造函数更直接。

---

不管怎样，一般来说，原则是你要在哪里创建一个带有a, b, c列的json对象, 你希望你可以只写非法语法:

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在输出上。