PostgreSQL でオブジェクトの JSON 配列として複数の列を効率的に取得する方法

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

このガイドでは、PostgreSQL テーブルの行を 1 つの列で効率的にグループ化し、残りの列をオブジェクトの JSON 配列に集約する方法を説明します。 MyTable の例を考えてみましょう:

<code>| 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 |</code>

目標は次のような出力を生成することです:

<code>| 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"}]</code>

単純な集計方法では、JSON オブジェクトに id とグループ化列 (value_two) が含まれることが多いため、これは避けたいことに注意してください。

さまざまな PostgreSQL バージョンに最適化されたソリューションを次に示します:

PostgreSQL 10 以降:

- 演算子を利用して不要なキーを除外します:

<code class="language-sql">SELECT value_two, jsonb_agg(to_jsonb(t.*) - '{id, value_two}'::text[]) AS value_four
FROM   MyTable t
GROUP  BY value_two;</code>

PostgreSQL 9.4 以降:

キーと値のペアを正確に制御するには、jsonb_build_object() を使用します。

<code class="language-sql">SELECT value_two, jsonb_agg(jsonb_build_object('value_three', value_three, 'value_four', value_four)) AS value_four
FROM   MyTable
GROUP  BY value_two;</code>

PostgreSQL 9.3 以降:

行式で to_jsonb() を使用します:

<code class="language-sql">SELECT value_two, jsonb_agg(to_jsonb(row(value_three, value_four))) AS value_four
FROM   MyTable
GROUP  BY value_two;</code>

これらのメソッドは、不要なキーを回避し、さまざまな PostgreSQL バージョンに適応しながら、目的の JSON 出力を生成する効率的かつクリーンな方法を提供します。 最適なパフォーマンスを得るために、データベースのバージョンと互換性のある方法を選択してください。

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