如何在 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

目標是建立一個 JSON 數組，其中每個物件包含 value_three 和 value_four，並按 value_two 分組。所需的輸出格式為：

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 及更高版本：

利用 - 運算子進行鍵排除可簡化查詢：

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

這可以在聚合之前有效地刪除不必要的列（id 和 val2）。

PostgreSQL 9.4 及更高版本：

jsonb_build_object()提供了更受控制的方法：

<code class="language-sql">SELECT val2, jsonb_agg(jsonb_build_object('val3', val3, 'val4', val4)) AS js_34
FROM tbl 
GROUP BY val2;</code>

這明確指定了每個 JSON 物件的鍵值對。

PostgreSQL 9.3 及更高版本：

有許多選擇，每個選擇都需要權衡：

1. to_jsonb((val3, val4))：這會遺失產生的 JSON 中的列名稱。

2. (val3, val4)::foo：這透過轉換為自訂行類型 (foo) 來保留列名稱。 需要事先建立類型 CREATE TYPE foo AS (val3 text, val4 text);。

3. 子查詢方法：使用子查詢的更詳細方法，但避免型別轉換。

<code class="language-sql">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;</code>

選擇最適合您的 PostgreSQL 版本和所需簡潔程度的方法。 對於版本 10 及更高版本，- 運算子提供最乾淨、最高效的解決方案。

以上是如何在 PostgreSQL 中以 JSON 物件陣列的形式傳回多列？的詳細內容。更多資訊請關注PHP中文網其他相關文章！