使用具有嵌套联接的 row_to_json()

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

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

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', '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;

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.

我得到了列名，因为我转换到了适当的记录类型，例如在该表的结果的情况下使用 ::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"
}
}
}
}
117749 次浏览
小开
最佳答案

更新: 在 PostgreSQL 9.4中，这对 随着 ABC0，ABC1，ABC2和 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"
}
}
}

顺便说一下，当你有一个1: many 关系时，你会想要使用 array_to_json(array_agg(...))。

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;

但 PostgreSQL 的 ROW构造函数不接受 AS列别名。

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

因为 CTE 是优化围栏，所以将嵌套子查询版本改为使用链式 CTE (WITH表达式)可能不会执行得很好，并且不会导致相同的计划。在这种情况下，在我们对 row_to_json进行一些改进或者更直接地覆盖 ROW构造函数中的列名之前，您可能只能使用丑陋的嵌套子查询。

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

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

或者:

(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}]}

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

这意味着您可以使用 作曲 json 操作来构造行，您不必总是创建非常复杂的 PostgreSQL 复合类型，然后在输出上调用 row_to_json。

小开

我对长期可维护性的建议是使用 VIEW 构建查询的粗略版本，然后使用如下函数:

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;

在这种情况下，对象 generence.users 是一个视图。因为我选择了用户。* ，如果需要更新视图以在用户记录中包含更多字段，则不必更新此函数。

小开

我添加这个解决方案是因为接受的响应没有考虑 N: N 关系。 aka: 对象集合的集合

如果你有 N: N 个关系，那么这个关系就是你的朋友。 在我的示例中，我希望构建以下层次结构的树视图。

A Requirement - Has - TestSuites
A Test Suite - Contains - TestCases.

以下查询表示联接。

SELECT reqId ,r.description as reqDesc ,array_agg(s.id)
s.id as suiteId , s."Name"  as suiteName,
tc.id as tcId , tc."Title"  as testCaseTitle


from "Requirement" r
inner join "Has"  h on r.id = h.requirementid
inner join "TestSuite" s on s.id  = h.testsuiteid
inner join "Contains" c on c.testsuiteid  = s.id
inner join "TestCase"  tc on tc.id = c.testcaseid
GROUP BY r.id, s.id;

由于不能进行多个聚合，因此需要使用“ WITH”。

with testcases as (
select  c.testsuiteid,ts."Name" , tc.id, tc."Title"  from "TestSuite" ts
inner join "Contains" c on c.testsuiteid  = ts.id
inner join "TestCase"  tc on tc.id = c.testcaseid


),
requirements as (
select r.id as reqId ,r.description as reqDesc , s.id as suiteId
from "Requirement" r
inner join "Has"  h on r.id = h.requirementid
inner join "TestSuite" s on s.id  = h.testsuiteid


)
, suitesJson as (
select  testcases.testsuiteid,
json_agg(
json_build_object('tc_id', testcases.id,'tc_title', testcases."Title" )
) as suiteJson
from testcases
group by testcases.testsuiteid,testcases."Name"
),
allSuites as (
select has.requirementid,
json_agg(
json_build_object('ts_id', suitesJson.testsuiteid,'name',s."Name"  , 'test_cases', suitesJson.suiteJson )
) as suites
from suitesJson inner join "TestSuite" s on s.id  = suitesJson.testsuiteid
inner join "Has" has on has.testsuiteid  = s.id
group by has.requirementid
),
allRequirements as (
select json_agg(
json_build_object('req_id', r.id ,'req_description',r.description , 'test_suites', allSuites.suites )
) as suites
from allSuites inner join "Requirement" r on r.id  = allSuites.requirementid


)
select * from allRequirements

它所做的就是构建一个 JSON 对象，并将它们聚合到每个 with子句上。

结果:

[
{
"req_id": 1,
"req_description": "<character varying>",
"test_suites": [
{
"ts_id": 1,
"name": "TestSuite",
"test_cases": [
{
"tc_id": 1,
"tc_title": "TestCase"
},
{
"tc_id": 2,
"tc_title": "TestCase2"
}
]
},
{
"ts_id": 2,
"name": "TestSuite",
"test_cases": [
{
"tc_id": 2,
"tc_title": "TestCase2"
}
]
}
]
},
{
"req_id": 2,
"req_description": "<character varying> 2 ",
"test_suites": [
{
"ts_id": 2,
"name": "TestSuite",
"test_cases": [
{
"tc_id": 2,
"tc_title": "TestCase2"
}
]
}
]
}
]

提交答案