如何在SQL Server中使用SQL查询为JSON对象添加数组方括号

你要实现的是将多个JSON对象包裹为合法JSON数组，不同数据库的实现方式如下：

通用思路 #

• 如果是通过子查询动态生成improvementType的内容，优先使用数据库自带的JSON聚合函数，自动生成数组格式，避免手动拼接出现转义错误
• 如果已经是拼接好的JSON对象字符串，直接在外层包裹[]即可，注意验证生成内容的JSON合法性

各数据库具体实现 #

MySQL #

场景1：子查询聚合生成

用JSON_ARRAYAGG搭配JSON_OBJECT直接生成数组：

SELECT 
  JSON_ARRAYAGG(
    JSON_OBJECT(
      'optionID', t.optionID,
      'optionGroupID', t.optionGroupID,
      'optionText', t.optionText,
      'optionGroupName', t.optionGroupName
    )
  ) AS improvementType
FROM 你的子查询表 t
WHERE 过滤条件;

场景2：已有JSON对象字符串

直接拼接后转JSON类型：

SELECT 
  CAST(CONCAT('[', 你现有的improvementType字符串字段, ']') AS JSON) AS improvementType
FROM 你的表;

PostgreSQL #

场景1：子查询聚合生成

用json_agg搭配json_build_object实现：

SELECT 
  json_agg(
    json_build_object(
      'optionID', t.optionID,
      'optionGroupID', t.optionGroupID,
      'optionText', t.optionText,
      'optionGroupName', t.optionGroupName
    )
  ) AS improvementType
FROM 你的子查询表 t
WHERE 过滤条件;

场景2：已有JSON对象字符串

SELECT 
  ('[' || 你现有的improvementType字符串字段 || ']')::json AS improvementType
FROM 你的表;

SQL Server #

场景1：子查询聚合生成

用FOR JSON PATH自动生成数组：

SELECT 
  optionID, optionGroupID, optionText, optionGroupName
FROM 你的子查询表 t
WHERE 过滤条件
FOR JSON PATH;

场景2：已有JSON对象字符串

SELECT 
  JSON_QUERY(CONCAT('[', 你现有的improvementType字符串字段, ']')) AS improvementType
FROM 你的表;

注意事项 #

尽量不要直接硬拼接字符串生成JSON，当字段值存在双引号、斜杠等特殊字符时，硬拼接会导致JSON格式非法，使用数据库原生JSON函数会自动处理转义逻辑，生成的内容更可靠。

内容的提问来源于stack exchange，提问作者Sudhakar Chitlam