带有嵌套连接的 PostgreSQL 9.2 row_to_json()
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13227142/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL 9.2 row_to_json() with nested joins
提问by dwerner
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.
我正在尝试使用row_to_json()在 PostgreSQL 9.2 中添加的函数将查询结果映射到 JSON 。
I'm having trouble figuring out the best way to represent joined rows as nested objects (1:1 relations)
我无法找出将连接行表示为嵌套对象的最佳方式（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', '[email protected]', 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 XXXstatements, as I think I should need in this case.
我发现如果我使用ROW()，我可以将结果字段分成一个子对象，但它似乎仅限于一个级别。我无法插入更多AS XXX语句，因为我认为在这种情况下我应该需要。
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.
我得到了列名，因为我强制转换为适当的记录类型，例如 with ::user_roles，在该表的结果的情况下。
Here's what that query returns:
这是该查询返回的内容：
{
"id":1,
"name":"Dan",
"email":"[email protected]",
"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:
我想要做的是以一种我可以添加连接的方式为连接生成 JSON（同样 1:1 很好），并将它们表示为它们加入的父对象的子对象，即如下所示：
{
"id":1,
"name":"Dan",
"email":"[email protected]",
"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"
}
}
}
}
Any help is appreciated. Thanks for reading.
任何帮助表示赞赏。谢谢阅读。
回答by Craig Ringer
Update: In PostgreSQL 9.4 this improves a lot with the introduction of to_json, json_build_object, json_objectand json_build_array, though it's verbose due to the need to name all the fields explicitly:
更新：在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;
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:
它不限于单排，只是有点痛苦。您不能使用别名为复合行类型添加别名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;
produces, via http://jsonprettyprint.com/:
通过http://jsonprettyprint.com/产生：
{
"id": 1,
"name": "Dan",
"email": "[email protected]",
"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.
array_to_json(array_agg(...))顺便说一句，当您有 1:many 关系时，您会想要使用。
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 ROWconstructor doesn't accept AScolumn aliases. Sadly.
...但 PostgreSQL 的ROW构造函数不接受AS列别名。可悲。
Thankfully, they optimize out the same. Compare the plans:
值得庆幸的是，他们优化了相同的内容。比较方案：
- The nested subquery version; vs
- The latter nested
ROWconstructor versionwith the aliases removed so it executes
- 该嵌套子查询的版本; 对比
- 后者的嵌套
ROW构造函数版本删除了别名，因此它执行
Because CTEs are optimisation fences, rephrasing the nested subquery version to use chained CTEs (WITHexpressions) 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_jsonor a way to override the column names in a ROWconstructor more directly.
由于 CTE 是优化栅栏，将嵌套子查询版本改写为使用链式 CTE（WITH表达式）可能效果不佳，并且不会产生相同的计划。在这种情况下，在我们row_to_json对ROW构造函数中的列名进行一些改进或更直接地覆盖列名之前，您会遇到难看的嵌套子查询。
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:
无论如何，一般来说，原则是您想要创建一个带有 columns 的 json 对象a, b, c，并且您希望您可以编写非法语法：
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 jsonvalues without additional quoting, e.g. if you put the output of a json_aggwithin a row_to_json, the inner json_aggresult won't get quoted as a string, it'll be incorporated directly as json.
此外，请记住，您可以在json没有额外引用的情况下组合值，例如，如果您将 a 的输出json_agg放在 a 中row_to_json，则内部json_agg结果不会被引用为字符串，它将直接作为 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_aggproduct, [{"a":1,"b":2}, {"a":1,"b":2}], hasn't been escaped again, as textwould be.
请注意，json_agg产品 ,[{"a":1,"b":2}, {"a":1,"b":2}]并没有再次被转义text。
This means you can composejson operations to construct rows, you don't always have to create hugely complex PostgreSQL composite types then call row_to_jsonon the output.
这意味着您可以组合json 操作来构造行，您不必总是创建非常复杂的 PostgreSQL 组合类型然后调用row_to_json输出。
回答by Todd
My suggestion for maintainability over the long term is to use a VIEW to build the coarse version of your query, and then use a function as below:
我对长期可维护性的建议是使用 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;
In this case, the object prominence.users is a view. Since I selected users.*, I will not have to update this function if I need to update the view to include more fields in a user record.
在这种情况下，对象 prominence.users 是一个视图。由于我选择了 users.*，如果我需要更新视图以在用户记录中包含更多字段，我将不必更新此函数。
回答by Gonzalo Del cerro
I am adding this solution becasue the accepted response does not contemplate N:N relationships. aka: collections of collections of objects
我添加这个解决方案是因为接受的响应没有考虑 N:N 关系。又名：对象集合的集合
If you have N:N relationships the clausula withit's your friend.
In my example, I would like to build a tree view of the following hierarchy.
如果您有 N:N 关系，则 clausulawith就是您的朋友。在我的示例中，我想构建以下层次结构的树视图。
A Requirement - Has - TestSuites
A Test Suite - Contains - TestCases.
The following query represents the joins.
以下查询表示连接。
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;
Since you can not do multiple aggregations, you need to use "WITH".
由于您不能进行多次聚合，因此您需要使用“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
What it does is building the JSON object in small collection of items and aggregating them on each withclausules.
它所做的是在项目的小集合中构建 JSON 对象，并将它们聚合到每个with条款上。
Result:
结果：
[
{
"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"
}
]
}
]
}
]
