将row_to_json()与嵌套连接一起使用
将row_to_json()与嵌套连接一起使用
提问于 2012-11-05 14:41:01
我正在尝试使用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"
}
}
}
}我想做的是为join生成JSON (同样1:1很好)，我可以添加join，并将它们表示为它们所连接到的父对象的子对象，即如下所示：
{
"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"
}
}
}
}回答 2
Stack Overflow用户
发布于 2020-04-18 15:55:34
我之所以添加这个解决方案，是因为接受的响应不考虑N:N关系。又名:对象集合的集合
如果你有N:N关系，with子句，它就是你的朋友。在我的示例中，我想构建以下层次结构的树视图。
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"
}
]
}
]
}
]Stack Overflow用户
发布于 2016-02-11 07:32:08
对于长期的可维护性，我的建议是使用视图来构建查询的粗略版本，然后使用如下函数：
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;在本例中，对象prominence.users是一个视图。由于我选择了users.*，因此如果需要更新视图以在用户记录中包含更多字段，则不必更新此函数。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/13227142
复制相关文章
相似问题