将 row_to_json() 与嵌套连接一起使用 [英] Using row_to_json() with nested joins

问题描述

我正在尝试使用 PostgreSQL 9.2 中添加的 row_to_json() 函数将查询结果映射到 JSON.

我无法找出将连接行表示为嵌套对象的最佳方式(1:1 关系)

这是我尝试过的(设置代码:表格、示例数据、后跟查询):

-- 一些测试表开始:创建表 role_duties (id 串行主键，名称 varchar);创建表 user_roles (id 串行主键，名称 varchar，描述 varchar,duty_id int, 外键 (duty_id) 引用 role_duties(id));创建表用户(id 串行主键，名称 varchar，电子邮件 varchar，user_role_id int, 外键 (user_role_id) 引用 user_roles(id));做$$声明 duty_id int;声明 role_id int;开始插入 role_duties (name) 值 ('Script Execution') 将 id 返回到 duty_id;插入 user_roles (name, description, duty_id) 值 ('admin', 'Administrative duty in the system', duty_id) 将 id 返回到 role_id 中；插入用户(姓名、电子邮件、user_role_id)值('Dan'、'[e-mail]'、role_id)；结束$$;

查询本身:

select row_to_json(row)从 (选择 u.*, ROW(ur.*::user_roles, ROW(d.*::role_duties)) 作为 user_role来自用户你内部加入 user_roles ur on ur.id = u.user_role_idd.id = ur.duty_id 上的内连接 role_duties d) 排;

我发现如果我使用 ROW()，我可以将结果字段分离到一个子对象中，但它似乎仅限于一个级别.我无法插入更多 AS XXX 语句，因为我认为在这种情况下我应该需要.

我得到了列名，因为我转换为适当的记录类型，例如使用 ::user_roles，在该表的结果的情况下.

这是该查询返回的内容:

<代码>{id":1，名称":丹"，电子邮件":[e-mail]"，user_role_id":1，用户角色":{f1":{id":1，名称":管理员"，描述":系统中的管理职责"，duty_id":1},f2":{f1":{id":1，名称":脚本执行"}}}}

我想要做的是以一种可以添加连接的方式为连接生成 JSON(同样 1:1 很好)，并将它们表示为它们加入的父对象的子对象，即如下所示:

<代码>{id":1，名称":丹"，电子邮件":[e-mail]"，user_role_id":1，用户角色":{id":1，名称":管理员"，描述":系统中的管理职责"，duty_id":1职责":{id":1，名称":脚本执行"}}}}

解决方案

更新:在 PostgreSQL 9.4 中，这改进了很多 引入了 to_json、json_build_object、json_object 和 json_build_array，虽然它是由于需要显式命名所有字段而变得冗长:

选择json_build_object('id', u.id,'name', u.name,'电子邮件', u.email,'user_role_id', u.user_role_id,'user_role', json_build_object('id', ur.id,'name', ur.name,'描述', ur.description,'duty_id', ur.duty_id,'职责', json_build_object('id', d.id,'名称', d.name)))来自用户你内部加入 user_roles ur on ur.id = u.user_role_id内连接 role_duties d on d.id = ur.duty_id;

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

<小时>

它不仅限于单行，只是有点痛苦.不能使用AS给复合行类型加上别名，所以需要使用别名子查询表达式或者CTE来达到效果:

select row_to_json(row)从 (选择 u.*, urd AS user_role来自用户你内部联接 (选择 ur.*, d来自 user_roles urd.id = ur.duty_id 上的内连接 role_duties d) urd(id,name,description,duty_id,duty) on urd.id = u.user_role_id) 排;

产生，通过http://jsonprettyprint.com/:

<代码>{身份证":1，"name": "丹","email": "[e-mail]","user_role_id": 1,用户角色":{身份证":1，"name": "管理员","description": "系统中的管理职责",duty_id":1，责任": {身份证":1，"name": "脚本执行"}}}

顺便说一句，当您有 1:many 关系时，您将需要使用 array_to_json(array_agg(...)).

理想情况下，上面的查询应该可以写成:

选择 row_to_json(ROW(u.*, ROW(ur.*, d AS duty) AS user_role))来自用户你内部加入 user_roles ur on ur.id = u.user_role_id内连接 role_duties d on d.id = ur.duty_id;

... 但是 PostgreSQL 的 ROW 构造函数不接受 AS 列别名.很遗憾.

值得庆幸的是，他们优化了相同的内容.比较计划:

• 嵌套子查询版本；对比
• 后者嵌套ROW构造函数版本，删除了别名以便执行

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

<小时>

无论如何，一般来说，原则是你想创建一个带有a, b, c列的json对象，并且你希望你能写出非法的语法:

ROW(a, b, c) AS 外层名(name1, name2, name3)

您可以改为使用返回行类型值的标量子查询:

(SELECT x FROM (SELECT a AS name1, b AS name2, c AS name3) x) AS 外层名

或者:

(SELECT x FROM (SELECT a, b, c) AS x(name1, name2, name3)) AS 外层名

此外，请记住，您可以组合 json 值而无需额外引用，例如如果您将 json_agg 的输出放在 row_to_json 中，则内部 json_agg 结果不会被引用为字符串，它将是直接合并为 json.

例如在任意示例中:

SELECT row_to_json((SELECT x FROM (SELECT1 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)，真的);

输出为:

{"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.

I'm trying to map the results of a query to JSON using the row_to_json() function that was added in PostgreSQL 9.2.

I'm having trouble figuring out the best way to represent joined rows as nested objects (1:1 relations)

Here's what I've tried (setup code: tables, sample data, followed by query):

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

The query itself:

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;

I found if I used ROW(), I could separate the resulting fields out into a child object, but it seems limited to a single level. I can't insert more AS XXX statements, as I think I should need in this case.

I am afforded column names, because I cast to the appropriate record type, for example with ::user_roles, in the case of that table's results.

Here's what that query returns:

{
   "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"
         }
      }
   }
}

What I want to do is generate JSON for joins (again 1:1 is fine) in a way where I can add joins, and have them represented as child objects of the parents they join to, i.e. like the following:

{
   "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"
         }
      }
   }
}

解决方案

Update: In PostgreSQL 9.4 this improves a lot with the introduction of to_json, json_build_object, json_object and json_build_array, though it's verbose due to the need to name all the fields explicitly:

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;

For older versions, read on.

---

It isn't limited to a single row, it's just a bit painful. You can't alias composite rowtypes using AS, so you need to use an aliased subquery expression or CTE to achieve the effect:

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;

produces, via 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"
    }
  }
}

You will want to use array_to_json(array_agg(...)) when you have a 1:many relationship, btw.

The above query should ideally be able to be written as:

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;

... but PostgreSQL's ROW constructor doesn't accept AS column aliases. Sadly.

Thankfully, they optimize out the same. Compare the plans:

• The nested subquery version; vs
• The latter nested ROW constructor version with the aliases removed so it executes

Because CTEs are optimisation fences, rephrasing the nested subquery version to use chained CTEs (WITH expressions) may not perform as well, and won't result in the same plan. In this case you're kind of stuck with ugly nested subqueries until we get some improvements to row_to_json or a way to override the column names in a ROW constructor more directly.

---

Anyway, in general, the principle is that where you want to create a json object with columns a, b, c, and you wish you could just write the illegal syntax:

ROW(a, b, c) AS outername(name1, name2, name3)

you can instead use scalar subqueries returning row-typed values:

(SELECT x FROM (SELECT a AS name1, b AS name2, c AS name3) x) AS outername

Or:

(SELECT x FROM (SELECT a, b, c) AS x(name1, name2, name3)) AS outername

Additionally, keep in mind that you can compose json values without additional quoting, e.g. if you put the output of a json_agg within a row_to_json, the inner json_agg result won't get quoted as a string, it'll be incorporated directly as json.

e.g. in the arbitrary example:

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

the output is:

{"k1":1,
 "k2":2,
 "k3":[{"a":1,"b":2}, 
 {"a":1,"b":2}]}

Note that the json_agg product, [{"a":1,"b":2}, {"a":1,"b":2}], hasn't been escaped again, as text would be.

This means you can compose json operations to construct rows, you don't always have to create hugely complex PostgreSQL composite types then call row_to_json on the output.

这篇关于将 row_to_json() 与嵌套连接一起使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！