PostgreSQLで複数の列をオブジェクトのJSON配列として返す方法は?

複数の PostgreSQL 列からオブジェクトの JSON 配列を生成する

このガイドでは、PostgreSQL テーブルからデータをオブジェクトの JSON 配列として取得し、特定の列ごとにグループ化する方法を説明します。 例では、MyTable テーブルを参照として使用します。

id	value_two	value_three	value_four
1	a	A	AA
2	a	A2	AA2
3	b	A3	AA3
4	a	A4	AA4
5	b	A5	AA5

目的は、各オブジェクトに value_three と value_four が含まれ、value_two でグループ化された JSON 配列を作成することです。必要な出力形式は次のとおりです:

value_two	value_four
a	[{"value_three":"A","value_four":"AA"}, {"value_three":"A2","value_four":"AA2"}, {"value_three":"A4","value_four":"AA4"}]
b	[{"value_three":"A3","value_four":"AA3"}, {"value_three":"A5","value_four":"AA5"}]

PostgreSQL ソリューション (バージョン固有)

最適なソリューションは、PostgreSQL のバージョンによって異なります。

PostgreSQL 10 以降:

キーの除外に - 演算子を利用すると、クエリが簡素化されます。

SELECT val2, jsonb_agg(to_jsonb(t.*) - '{id, val2}'::text[]) AS js_34
FROM tbl t
GROUP BY val2;

これにより、集計前に不要な列 (id と val2) が効率的に削除されます。

PostgreSQL 9.4 以降:

jsonb_build_object() は、より制御されたアプローチを提供します。

SELECT val2, jsonb_agg(jsonb_build_object('val3', val3, 'val4', val4)) AS js_34
FROM tbl 
GROUP BY val2;

これは、各 JSON オブジェクトのキーと値のペアを明示的に指定します。

PostgreSQL 9.3 以降:

いくつかのオプションがあり、それぞれにトレードオフがあります:

1. to_jsonb((val3, val4)): これにより、結果の JSON 内の列名が失われます。

2. (val3, val4)::foo: カスタム行タイプ (foo) にキャストすることで列名を保持します。 事前にタイプ CREATE TYPE foo AS (val3 text, val4 text); を作成する必要があります。

3. サブクエリのアプローチ: サブクエリを使用するより冗長なメソッドですが、型キャストは回避されます。

SELECT val2, jsonb_agg(to_jsonb((val3, val4))) AS js_34  -- Loses column names
FROM tbl
GROUP BY val2;

-- Requires: CREATE TYPE foo AS (val3 text, val4 text);
SELECT val2, jsonb_agg((val3, val4)::foo) AS js_34  -- Preserves column names
FROM tbl
GROUP BY val2;

SELECT val2, jsonb_agg(to_jsonb((SELECT t FROM (SELECT val3, val4) t))) AS js_34 -- Subquery approach
FROM tbl
GROUP BY val2;

PostgreSQL のバージョンと必要な簡潔さのレベルに最も適した方法を選択してください。 バージョン 10 以降の場合、- オペレーターは最もクリーンで効率的なソリューションを提供します。

以上がPostgreSQLで複数の列をオブジェクトのJSON配列として返す方法は?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。