在 PostgreSQL 中，返回多列作为 JSON 对象数组是一个常见的需求，尤其是当你需要以结构化的格式将多个列的数据转换成 JSON 输出时。这种格式非常适用于 API 数据输出、报告生成和其他需要结构化数据的场景。幸运的是，PostgreSQL 提供了强大的 JSON 函数，可以非常方便地实现这一功能。

1. PostgreSQL 的 JSON 类型和函数

PostgreSQL 支持两种 JSON 类型：

• JSON：用于存储标准的 JSON 数据，存储为文本格式。
• JSONB：用于存储二进制格式的 JSON 数据，提供更高效的查询性能和更好的索引支持。

你可以使用这些类型的函数来提取、创建和操作 JSON 数据。特别是 json_build_object 和 jsonb_build_object 函数，它们非常适合将多个列组合成 JSON 对象。

2. 如何在 PostgreSQL 中以 JSON 对象数组的形式返回多列

假设你有一个 employees 表，包含如下列：

• id：员工的唯一标识符。
• name：员工的姓名。
• position：员工的职位。
• salary：员工的薪资。

我们希望将这几列的内容作为 JSON 对象数组返回。以下是实现的步骤和示例。

示例 1：使用 jsonb_agg 和 jsonb_build_object 返回 JSON 对象数组

SELECT jsonb_agg(
           jsonb_build_object(
               'id', id,
               'name', name,
               'position', position,
               'salary', salary
           )
       ) AS employee_json
FROM employees;

在这个查询中：

• jsonb_build_object('id', id, 'name', name, 'position', position, 'salary', salary)：此函数将每一行的数据转换为一个 JSON 对象，每个键值对对应表中的一列。
• jsonb_agg()：将所有的 JSON 对象聚合成一个 JSON 数组。

假设 employees 表中有如下数据：

id	name	position	salary
1	John	Developer	50000
2	Alice	Designer	60000
3	Bob	Manager	70000

上述查询的结果将是一个包含所有员工信息的 JSON 数组：

[
  {"id": 1, "name": "John", "position": "Developer", "salary": 50000},
  {"id": 2, "name": "Alice", "position": "Designer", "salary": 60000},
  {"id": 3, "name": "Bob", "position": "Manager", "salary": 70000}
]

示例 2：返回带有嵌套 JSON 对象的 JSON 数组

你还可以根据需要返回带有嵌套 JSON 对象的 JSON 数组。例如，假设你希望将 position 和 salary 信息嵌套在一个子对象中，而 id 和 name 作为父级对象。

SELECT jsonb_agg(
           jsonb_build_object(
               'id', id,
               'name', name,
               'details', jsonb_build_object(
                   'position', position,
                   'salary', salary
               )
           )
       ) AS employee_json
FROM employees;

这将生成如下的 JSON 结果：

[
  {"id": 1, "name": "John", "details": {"position": "Developer", "salary": 50000}},
  {"id": 2, "name": "Alice", "details": {"position": "Designer", "salary": 60000}},
  {"id": 3, "name": "Bob", "details": {"position": "Manager", "salary": 70000}}
]

3. 使用 row_to_json 提取多列数据

除了 jsonb_build_object，你还可以使用 row_to_json() 函数，该函数会将整个行转换为一个 JSON 对象。使用这种方法时，查询结果会包含表中所有列的内容，默认会将列名作为 JSON 对象的键。

例如，以下查询将 employees 表的所有列作为 JSON 对象数组返回：

SELECT jsonb_agg(row_to_json(employees)) AS employee_json
FROM employees;

假设 employees 表的列有 id、name、position 和 salary，执行该查询将得到类似于以下格式的 JSON 结果：

[
  {"id": 1, "name": "John", "position": "Developer", "salary": 50000},
  {"id": 2, "name": "Alice", "position": "Designer", "salary": 60000},
  {"id": 3, "name": "Bob", "position": "Manager", "salary": 70000}
]

这种方法更简便，但你无法完全控制 JSON 对象的键（它会自动使用表的列名）。

4. 返回特定条件下的 JSON 对象数组

你还可以根据特定条件筛选数据，并以 JSON 对象数组的形式返回。例如，假设你只想查询职位为 “Developer” 的员工，并将结果作为 JSON 对象数组返回：

SELECT jsonb_agg(
           jsonb_build_object(
               'id', id,
               'name', name,
               'position', position,
               'salary', salary
           )
       ) AS developer_json
FROM employees
WHERE position = 'Developer';

返回的 JSON 结果将只包含符合条件的数据：

[
  {"id": 1, "name": "John", "position": "Developer", "salary": 50000}
]

5. 性能优化：JSONB 类型和索引

如果你需要更高效的查询，尤其是在处理大数据集时，建议使用 jsonb 类型，而非 json。jsonb 提供了更好的索引支持和查询性能。例如，你可以为 jsonb 类型的列创建索引，从而加速查询操作。

创建 JSONB 索引示例：

CREATE INDEX idx_employee_jsonb ON employees USING gin (employee_json);

6. 总结

在 PostgreSQL 中，返回多列作为 JSON 对象数组是一个非常实用的功能，特别是在处理结构化数据和 API 响应时。通过使用如 jsonb_agg()、jsonb_build_object()、row_to_json() 等函数，你可以轻松将多个列的值聚合为 JSON 数组或嵌套对象。不同的函数和方法适用于不同的场景，你可以根据实际需求灵活选择。