PostgreSQL이 쿼리 결과를 하나의 JSON 배열로 반환하도록하고 싶습니다. 주어진
create table t (a int primary key, b text);
insert into t values (1, 'value1');
insert into t values (2, 'value2');
insert into t values (3, 'value3');
나는 비슷한 것을 원한다
[{"a":1,"b":"value1"},{"a":2,"b":"value2"},{"a":3,"b":"value3"}]
또는
{"a":[1,2,3], "b":["value1","value2","value3"]}
(실제로 둘 다 아는 것이 더 유용 할 것입니다). 나는 같은 것을 시도했다
select row_to_json(row) from (select * from t) row;
select array_agg(row) from (select * from t) row;
select array_to_string(array_agg(row), '') from (select * from t) row;
그리고 나는 가까이 있다고 생각하지만 실제로는 그렇지 않습니다. 9.15를 제외한 다른 문서를보고 있어야 합니다. JSON 함수와 연산자 ?
그건 그렇고, 나는 내 생각에 대해 잘 모르겠습니다. 이것은 일반적인 디자인 결정입니까? 내 생각은 물론 위의 세 가지 쿼리 중 첫 번째 쿼리의 결과 (예를 들어)를 가져 와서 클라이언트에 제공하기 전에 애플리케이션에서 약간 조작 할 수 있지만 PostgreSQL이 최종 JSON 객체를 직접 생성 할 수 있다면 응용 프로그램에 JSON 라이브러리에 대한 종속성을 포함하지 않았기 때문에 더 간단 할 것입니다.
답변
TL; DR
SELECT json_agg(t) FROM t
객체의 JSON 배열 및
SELECT
json_build_object(
'a', json_agg(t.a),
'b', json_agg(t.b)
)
FROM t
배열의 JSON 객체에 대해.
개체 목록
이 섹션에서는 각 행이 단일 객체로 변환되는 JSON 객체 배열을 생성하는 방법을 설명합니다. 결과는 다음과 같습니다.
[{"a":1,"b":"value1"},{"a":2,"b":"value2"},{"a":3,"b":"value3"}]
9.3 이상
이 json_agg
함수는이 결과를 즉시 생성합니다. 입력을 JSON으로 변환하고 배열로 집계하는 방법을 자동으로 파악합니다.
SELECT json_agg(t) FROM t
이 더되는 jsonb
버전 (9.4에서 소개하지 않음) json_agg
. 행을 배열로 집계 한 다음 변환 할 수 있습니다.
SELECT to_jsonb(array_agg(t)) FROM t
또는 json_agg
캐스트와 결합 :
SELECT json_agg(t)::jsonb FROM t
내 테스트에 따르면 먼저 어레이로 집계하는 것이 조금 더 빠릅니다. 캐스트가 전체 JSON 결과를 구문 분석해야하기 때문이라고 생각합니다.
9.2
9.2에는 json_agg
또는 to_json
함수가 없으므로 이전 버전을 사용해야합니다 array_to_json
.
SELECT array_to_json(array_agg(t)) FROM t
선택적으로 row_to_json
쿼리에 호출을 포함 할 수 있습니다 .
SELECT array_to_json(array_agg(row_to_json(t))) FROM t
이렇게하면 각 행이 JSON 개체로 변환되고 JSON 개체가 배열로 집계 된 다음 배열이 JSON 배열로 변환됩니다.
나는 둘 사이의 중요한 성능 차이를 식별 할 수 없었습니다.
목록의 대상
이 섹션에서는 각 키가 테이블의 열이고 각 값이 열 값의 배열 인 JSON 개체를 생성하는 방법을 설명합니다. 다음과 같은 결과입니다.
{"a":[1,2,3], "b":["value1","value2","value3"]}
9.5 이상
다음 json_build_object
기능을 활용할 수 있습니다 .
SELECT
json_build_object(
'a', json_agg(t.a),
'b', json_agg(t.b)
)
FROM t
열을 집계하여 단일 행을 만든 다음이를 객체로 변환 할 수도 있습니다.
SELECT to_json(r)
FROM (
SELECT
json_agg(t.a) AS a,
json_agg(t.b) AS b
FROM t
) r
객체에 원하는 이름이 있는지 확인하려면 배열의 별칭을 지정해야합니다.
어느 것이 더 명확한지는 의견의 문제입니다. 사용하는 경우json_build_object
함수를 가독성을 높이기 위해 키 / 값 쌍을 한 줄에 배치하는 것이 좋습니다.
array_agg
대신 사용할 수도 json_agg
있지만 내 테스트에 따르면 json_agg
약간 더 빠릅니다.
함수의 jsonb
버전 이 없습니다 json_build_object
. 단일 행으로 집계하고 다음을 변환 할 수 있습니다.
SELECT to_jsonb(r)
FROM (
SELECT
array_agg(t.a) AS a,
array_agg(t.b) AS b
FROM t
) r
그 결과 이런 종류의 다른 쿼리와는 달리, array_agg
사용하는 경우 조금 더 빠른 것 같다 to_jsonb
. 이것은 오버 헤드 파싱 및 JSON 결과 유효성 검사 때문이라고 생각합니다 json_agg
.
또는 명시 적 캐스트를 사용할 수 있습니다.
SELECT
json_build_object(
'a', json_agg(t.a),
'b', json_agg(t.b)
)::jsonb
FROM t
이 to_jsonb
버전을 사용하면 캐스트를 피할 수 있으며 내 테스트에 따르면 더 빠릅니다. 다시 말하지만 이것이 결과를 구문 분석하고 유효성을 검사하는 오버 헤드 때문이라고 생각합니다.
9.4 및 9.3
이 json_build_object
함수는 9.5에 새로 추가되었으므로 이전 버전에서는 집계하고 개체로 변환해야합니다.
SELECT to_json(r)
FROM (
SELECT
json_agg(t.a) AS a,
json_agg(t.b) AS b
FROM t
) r
또는
SELECT to_jsonb(r)
FROM (
SELECT
array_agg(t.a) AS a,
array_agg(t.b) AS b
FROM t
) r
원하는지 json
또는 jsonb
.
(9.3에는 jsonb
.)
9.2
9.2에서는 to_json
존재 하지 않습니다 . 다음을 사용해야합니다 row_to_json
.
SELECT row_to_json(r)
FROM (
SELECT
array_agg(t.a) AS a,
array_agg(t.b) AS b
FROM t
) r
선적 서류 비치
JSON 함수에서 JSON 함수 에 대한 문서를 찾습니다 .
json_agg
에 집계 함수의 페이지입니다.
디자인
성능이 중요한 경우 내 테스트를 신뢰하지 말고 자신의 스키마 및 데이터에 대해 쿼리를 벤치마킹하십시오.
좋은 디자인인지 아닌지는 특정 응용 프로그램에 따라 다릅니다. 유지 관리 측면에서는 특별한 문제가 없습니다. 이는 앱 코드를 단순화하고 앱의 해당 부분에서 유지 관리 할 것이 적다는 것을 의미합니다. PG가 즉시 필요한 결과를 정확하게 제공 할 수 있다면 사용하지 않을 것이라고 생각할 수있는 유일한 이유는 성능 고려 사항입니다. 바퀴와 모든 것을 재발 명하지 마십시오.
널
집계 함수는 일반적으로 NULL
0 행에서 작동 할 때 반환 됩니다. 이것이 가능성이있는 경우이를 COALESCE
피하는 데 사용할 수 있습니다. 몇 가지 예 :
SELECT COALESCE(json_agg(t), '[]'::json) FROM t
또는
SELECT to_jsonb(COALESCE(array_agg(t), ARRAY[]::t[])) FROM t
에 신용 한스 Landeholm 에 대한 이 지적
답변
또한 테이블에서 선택한 필드를 원하는 경우 배열로 집계합니다.
SELECT json_agg(json_build_object('data_a',a,
'data_b',b,
)) from t;
결과가 올 것입니다.
[{'data_a':1,'data_b':'value1'}
{'data_a':2,'data_b':'value2'}]