如何在 PostgreSQL 中有效率地檢索多個欄位作為 JSON 物件陣列？

PostgreSQL：從多列產生物件的 JSON 陣列

本指南示範如何透過單列有效地將 PostgreSQL 表中的行分組，並將其餘列聚合到 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>

這些方法提供了高效、簡潔的方法來產生所需的 JSON 輸出，避免不必要的金鑰並適應各種 PostgreSQL 版本。 選擇與您的資料庫版本相容的方法以獲得最佳效能。

以上是如何在 PostgreSQL 中有效率地檢索多個欄位作為 JSON 物件陣列？的詳細內容。更多資訊請關注PHP中文網其他相關文章！