如何在 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中文网其他相关文章！