중첩 조인이 있는 row_to_json() 사용
다음을 사용하여 쿼리 결과를 JSON에 매핑하려고 합니다.row_to_json()Postgre에 추가된 함수SQL 9.2.
조인된 행을 중첩된 개체(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"
}
}
}
}
업데이트: Postgre에서SQL 9.4에서는 , , 및 의 도입으로 많은 기능이 향상되었지만 모든 필드의 이름을 명시적으로 지정해야 하기 때문에 자세한 내용은 다음과 같습니다.
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(...))당신이 1: 다수의 관계를 가질 때, btw.
위의 쿼리는 이상적으로 다음과 같이 작성할 수 있어야 합니다.
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;
하지만 포스트그레SQL의ROW생성자가 수락하지 않음AS열 별칭.슬프게.
고맙게도, 그들은 동일한 것을 최적화합니다.계획 비교:
CTE는 최적화 울타리이기 때문에 연결된 CTE를 사용하도록 중첩된 하위 쿼리 버전을 바꿉니다.WITH표현식)이 제대로 수행되지 않을 수 있으며 동일한 계획이 발생하지 않습니다.이 경우, 당신은 우리가 몇 가지 개선을 얻을 때까지 추한 중첩 하위 쿼리에 갇혀 있습니다.row_to_json또는 의 열 이름을 재정의하는 방법ROW더 직접적으로 생성자.
에 json 객체를 만드는 입니다.a, b, c그리고 당신은 단지 불법적인 구문을 쓸 수 있기를 바랍니다.
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를 들어, " 추견적없값예입력니다합을경이우가입는하"(예: 의출을력력입는예하경(▁values▁a▁without,우▁of▁addition▁you▁putal▁quoting)의 출력을 입력할 경우 추가 견적 없이 값을 입력할 수 있습니다.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 작업을 작성할 수 있습니다. 항상 매우 복잡한 Postgre를 작성할 필요는 없습니다.SQL 복합 유형을 호출합니다.row_to_json산출물에
승인된 답변은 N:N 관계를 고려하지 않기 때문에 이 솔루션을 추가합니다.ka: 객체 컬렉션
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"
}
]
}
]
}
]
장기적인 유지보수성을 위해 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;
이 경우 개체 돌출.users는 보기입니다.users.*를 선택했으므로 사용자 레코드에 더 많은 필드를 포함하도록 보기를 업데이트해야 하는 경우 이 기능을 업데이트할 필요가 없습니다.
언급URL : https://stackoverflow.com/questions/13227142/using-row-to-json-with-nested-joins
'programing' 카테고리의 다른 글
| 이미지가 Visual Studio 디자이너에 표시되지만 런타임에는 표시되지 않음 (0) | 2023.05.18 |
|---|---|
| VB.NET에서 문자열을 연결하기 위한 앰퍼샌드 vs. (0) | 2023.05.18 |
| 콘솔에 Swift 사전을 예쁘게 인쇄할 수 있는 방법이 있습니까? (0) | 2023.05.18 |
| Angular 2+에서 spec.ts 파일 없이 구성 요소를 생성하는 중 (0) | 2023.05.18 |
| Azure Service Fabric에서 상태 저장 서비스를 사용해야 하는 시기와 외부 지속성에 의존해야 하는 시기 이해 (0) | 2023.05.18 |