[json] PostgreSQL은 JSON 배열로 결과 집합을 반환합니까?

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가 즉시 필요한 결과를 정확하게 제공 할 수 있다면 사용하지 않을 것이라고 생각할 수있는 유일한 이유는 성능 고려 사항입니다. 바퀴와 모든 것을 재발 명하지 마십시오.

집계 함수는 일반적으로 NULL0 행에서 작동 할 때 반환 됩니다. 이것이 가능성이있는 경우이를 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'}]


답변