如何在SQL Server中保留字段类型构建指定字段的JSON

问题背景 #

现有如下JSON结构的数据：

{
  "_key": {
    "id": "3b8d1ed7-c3ec-32ec-a083-2ddc17152e94",
    "rootId": "15c85327-9628-3685-b84a-375b546ba92a"
  },
  "employeeInfo": {
    "idNumber": "3",
    "gender": "Male",
    "active": true,
    "age": 20
  },
  "product": {
    "plan": "prod",
    "class": "1",
    "available": true,
    "_type": "Product"
  }
}

需要生成包含指定字段的新JSON：筛选employeeInfo的部分字段（如age/active/gender），同时保留product完整对象。之前采用字符串拼接JSON_VALUE的方式构建查询，会导致字符串类型字段丢失引号（比如gender: Male而非gender: "Male"），且无法手动添加引号（需运行时动态接收字段名）。

预期返回结果：

{ 
  "employeeInfo": {"age": 20, "active": true, "gender": "Male" }, 
  "product":{
    "plan": "prod",
    "class": "1",
    "available": true,
    "_type": "Product"
  }
}

SQL Server 解决方案 #

利用FOR JSON PATH自动处理类型格式，结合JSON_QUERY保留完整子对象，同时支持动态构建查询。

1. 静态字段查询实现 #

如果字段固定，直接通过嵌套FOR JSON构建子对象，外层再组合完整JSON：

SELECT
  -- 构建筛选后的employeeInfo子对象
  JSON_QUERY((
    SELECT
      c.info.value('$.employeeInfo.age', 'int') AS age,
      c.info.value('$.employeeInfo.active', 'bit') AS active,
      c.info.value('$.employeeInfo.gender', 'nvarchar(50)') AS gender
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  )) AS employeeInfo,
  -- 直接保留完整的product对象
  JSON_QUERY(c.info, '$.product') AS product
FROM item.[Item] AS c
INNER JOIN (
  SELECT "rootId", MAX("revisionNo") AS maxRevisionNo 
  FROM item."Item"
  WHERE "rootId" = 'E3B455EF-D48E-338C-B6D4-FFD8B41243F9' 
  GROUP BY "rootId"
) AS subquery 
  ON c."rootId" = subquery."rootId"
-- 外层组合成最终JSON
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

2. 动态字段查询实现 #

若需运行时动态接收字段名，可通过动态SQL拼接查询语句：

DECLARE @employeeFields NVARCHAR(MAX) = N'age,active,gender'; -- 动态传入的employeeInfo字段列表
DECLARE @keepFullObjects NVARCHAR(MAX) = N'product'; -- 动态传入的需保留完整对象的字段
DECLARE @sql NVARCHAR(MAX);

-- 构建employeeInfo的字段选择逻辑，自动映射类型
SET @employeeFields = STRING_AGG(
  CONCAT(
    N'c.info.value(''$.employeeInfo.', QUOTENAME(value, ''''), ''', ''', 
    -- 根据字段名映射SQL类型，可扩展更多字段
    CASE value 
      WHEN 'age' THEN 'int'
      WHEN 'active' THEN 'bit'
      WHEN 'gender' THEN 'nvarchar(50)'
      ELSE 'nvarchar(max)' -- 默认字符串类型
    END, 
    ''') AS ', QUOTENAME(value)
  ),
  N', '
) FROM STRING_SPLIT(@employeeFields, ',');

-- 构建完整的动态SQL语句
SET @sql = N'
SELECT
  JSON_QUERY((
    SELECT ' + @employeeFields + N'
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  )) AS employeeInfo,
  ' + 
  -- 构建保留完整对象的字段列表
  STRING_AGG(
    CONCAT(N'JSON_QUERY(c.info, ''$.', QUOTENAME(value, ''''), ''') AS ', QUOTENAME(value)),
    N', '
  ) FROM STRING_SPLIT(@keepFullObjects, ',') + N'
FROM item.[Item] AS c
INNER JOIN (
  SELECT "rootId", MAX("revisionNo") AS maxRevisionNo 
  FROM item."Item"
  WHERE "rootId" = ''E3B455EF-D48E-338C-B6D4-FFD8B41243F9'' 
  GROUP BY "rootId"
) AS subquery 
  ON c."rootId" = subquery."rootId"
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER';

-- 执行动态SQL
EXEC sp_executesql @sql;

方案说明 #

• FOR JSON PATH会自动根据SQL字段类型生成正确的JSON格式：字符串带引号、布尔值/数字保持原生类型，避免手动拼接的类型丢失问题。
• JSON_QUERY用于提取完整的子对象，避免被FOR JSON自动转义为字符串。
• 动态实现通过STRING_SPLIT拆分传入的字段列表，STRING_AGG拼接查询语句，适配运行时动态字段需求。

内容的提问来源于stack exchange，提问作者Valeriy K.