質問

PostgreSQLがクエリの結果を1つの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の関数と演算子 ?

ところで、私は自分の考えに自信がありません。これは通常の設計上の判断なのでしょうか？私の考えでは、もちろん、上記の3つのクエリのうち最初のクエリの結果（例）を取り、それをクライアントに提供する前にアプリケーションで少し操作することは可能ですが、もし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オブジェクトに変換します。

オブジェクトのリスト

ここでは、各行が1つのオブジェクトに変換された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配列に変換するものです。

この2つの間に大きな性能差は見出せませんでした。

リストのオブジェクト

ここでは、各キーがテーブルのカラム、各値がカラムの値の配列である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

また、列を集約して1つの行を作成し、それをオブジェクトに変換することも可能です。

SELECT to_json(r)
FROM (
    SELECT
        json_agg(t.a) AS a,
        json_agg(t.b) AS b
    FROM t
) r

なお、配列のエイリアスは、オブジェクトが希望する名前を持つようにするために絶対に必要です。

どちらが分かりやすいかは意見が分かれるところです。もし json_build_object 関数は、読みやすさを向上させるために、1つのキーと値のペアを1行に置くことを強くお勧めします。

を使用することもできます。 array_agg の代わりに json_agg しかし、私のテストでは json_agg の方が若干速いです。

はありません。 jsonb のバージョンは json_build_object 関数を使用します。1行に集約して変換することができます。

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を使えば、必要な結果をすぐに得られるのであれば、使わない理由はパフォーマンスに関するものだけでしょう。車輪を再発明するようなことはしないでください。

Nulls

集計関数の一般的な還元 NULL は、0行に対して操作した場合です。このような可能性がある場合は COALESCE を使用して、それらを回避することができます。いくつか例を挙げます。

SELECT COALESCE(json_agg(t), '[]'::json) FROM t

または

SELECT to_jsonb(COALESCE(array_agg(t), ARRAY[]::t[])) FROM t

<サブ クレジット ハンネス・ランデホルム について ご指摘