如何使用SQL的JSON_ARRAY和JSON_OBJECT高效构建JSON数据

在SQL开发场景中，将关系型数据转换为JSON格式是常见需求，JSON_ARRAY和JSON_OBJECT作为SQL内置的JSON构建函数，能够直接基于查询结果生成结构化的JSON数据，无需在应用层做额外的转换处理。

基础用法介绍

JSON_ARRAY函数

JSON_ARRAY用于将多个值组合成一个JSON数组，函数的参数是数组的元素，支持传入字段、常量、表达式等多种类型的值，最终返回的是一个合法的JSON数组字符串。

基础语法如下：

-- 构建简单JSON数组
SELECT JSON_ARRAY(1, 'test', 3.14, TRUE) AS result;
-- 输出结果：[1, "test", 3.14, true]

如果传入的是查询字段，会直接取对应行的字段值作为数组元素：

-- 假设存在user表，包含id和name字段
SELECT JSON_ARRAY(id, name) AS user_arr FROM user WHERE id = 1;

JSON_OBJECT函数

JSON_OBJECT用于构建JSON对象，参数需要成对出现，奇数位是键名，偶数位是对应的值，同样支持字段、常量、表达式作为参数，返回合法的JSON对象字符串。

基础语法如下：

-- 构建简单JSON对象
SELECT JSON_OBJECT('id', 1, 'name', '张三', 'age', 25) AS result;
-- 输出结果：{"id": 1, "name": "张三", "age": 25}

键名必须是字符串类型，值可以是任意合法的SQL表达式：

SELECT JSON_OBJECT('total', 10 + 20, 'is_valid', 1 > 0) AS result;
-- 输出结果：{"total": 30, "is_valid": true}

常见构建技巧

嵌套构建复杂结构

JSON_ARRAY和JSON_OBJECT支持互相嵌套，用来构建多层级的JSON结构，比如对象里包含数组，或者数组里包含对象。

示例：构建包含用户基本信息和标签列表的JSON对象：

-- 假设user表有id、name字段，user_tag表有user_id、tag字段
SELECT 
  JSON_OBJECT(
    'user_id', u.id,
    'user_name', u.name,
    'tags', JSON_ARRAY(
      (SELECT tag FROM user_tag WHERE user_id = u.id LIMIT 1),
      (SELECT tag FROM user_tag WHERE user_id = u.id LIMIT 1 OFFSET 1)
    )
  ) AS user_info
FROM user u WHERE u.id = 1;

结合聚合函数构建批量数据

如果需要将多行数据合并成一个JSON数组，可以结合GROUP_CONCAT（MySQL）或者STRING_AGG（PostgreSQL）等聚合函数使用，先拼接多个JSON对象，再包装成数组。

MySQL示例：将同一部门的所有员工信息构建成一个JSON数组：

-- 假设employee表有dept_id、emp_id、emp_name字段
SELECT 
  dept_id,
  JSON_ARRAY(
    GROUP_CONCAT(
      JSON_OBJECT('emp_id', emp_id, 'emp_name', emp_name)
    )
  ) AS dept_emps
FROM employee 
GROUP BY dept_id;

处理空值和特殊类型

当传入的参数为NULL时，JSON_ARRAY和JSON_OBJECT会将其转为JSON的null值，如果需要自定义空值处理逻辑，可以结合COALESCE函数使用。

-- 当name为NULL时，使用默认值未知
SELECT JSON_OBJECT('id', id, 'name', COALESCE(name, '未知')) AS result FROM user;

如果需要构建包含HTML特殊字符的内容，函数会自动处理转义，无需手动操作：

SELECT JSON_OBJECT('content', '<p>测试内容</p>') AS result;
-- 输出结果：{"content": "<p>测试内容</p>"}

注意事项

• 不同数据库对JSON函数的支持略有差异，比如MySQL 5.7+、PostgreSQL 9.4+都支持这两个函数，但部分低版本数据库可能不兼容，使用前需要确认数据库版本。
• JSON_OBJECT的键名如果包含特殊字符或者中文，不需要额外加转义，函数会自动处理编码问题。
• 构建复杂JSON时，建议先拆分单个部分的构建逻辑，验证正确后再组合嵌套，避免语法错误。

总结

JSON_ARRAY和JSON_OBJECT是SQL中构建JSON数据的核心工具，掌握它们的基础用法和嵌套技巧，能够快速实现关系型数据到JSON格式的转换，减少应用层的处理逻辑。在实际使用中，结合查询条件和聚合函数，可以满足大部分JSON构建场景的需求，提升数据处理的整体效率。

SQLJSON_ARRAYJSON_OBJECTJSON构建修改时间：2026-07-10 09:27:21