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