如何在SQL Server中用JSON_VALUE构建保留类型的新JSON？

现有JSON结构如下：

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

需要提取指定字段生成新JSON，当前使用的SQL查询手动拼接字符串导致类型丢失（字符串值不带引号）：

SELECT 
    '{ "employeeInfo": {"age: ' + 
    JSON_VALUE(info, '$.employeeInfo.age') + ', "gender": ' + 
    JSON_VALUE(info, '$.employeeInfo.gender') + ' }' AS info
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";

得到的结果：

{ "employeeInfo": {"age: 38, "gender": Female }

期望保留原类型的结果：

{ "employeeInfo": {"age: 38, "gender": "Female" }

使用环境：Microsoft Azure SQL Edge Developer (RTM) - 15.0.2000.1552 (ARM64)
已拥有PostgreSQL解决方案：

SELECT jsonb_strip_nulls(json_build_object('employee_info', json_build_object('age', c."info"->'employeeInfo' -> 'age', 'gender', c."info"->'employeeInfo' -> 'gender'), 'product', c."info"->'product')::jsonb) as info ...

需求：为SQL Server编写支持动态字段选择的查询方案，同时支持提取product.plan等任意字段。

1. 静态字段提取（自动保留类型） #

如果要提取的字段是固定的，推荐用以下两种方式避免手动拼接的类型问题：

方式A：使用JSON_QUERY保留原类型 #

JSON_QUERY会返回原始的JSON片段（带正确的类型格式），直接拼接即可：

SELECT 
    JSON_QUERY('{
        "employeeInfo": {
            "age": ' + JSON_QUERY(info, '$.employeeInfo.age') + ',
            "gender": ' + JSON_QUERY(info, '$.employeeInfo.gender') + '
        },
        "product": {
            "plan": ' + JSON_QUERY(info, '$.product.plan') + '
        }') AS info
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";

方式B：用FOR JSON PATH自动构造JSON（更可靠） #

通过将提取的字段作为SQL列，再用FOR JSON PATH生成最终JSON，自动处理类型映射：

SELECT 
    (SELECT 
        JSON_VALUE(info, '$.employeeInfo.age') AS age,
        JSON_VALUE(info, '$.employeeInfo.gender') AS gender
     FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) AS employeeInfo,
    (SELECT 
        JSON_VALUE(info, '$.product.plan') AS plan
     FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) AS product
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER AS info
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会根据SQL列的类型自动生成对应JSON类型（字符串带引号、数字/布尔保持原样）。

2. 动态字段选择（支持任意字段） #

如果需要动态指定要提取的字段（比如从参数传入），可以用动态SQL结合FOR JSON PATH实现：

实现步骤 #

1. 定义字段映射参数：用JSON格式指定要提取的源路径和目标键（支持嵌套结构）
2. 动态生成SQL语句：根据字段映射生成列定义，再用FOR JSON PATH生成最终JSON

示例代码：

-- 定义要提取的字段：源JSON路径 -> 目标JSON键
DECLARE @fields NVARCHAR(MAX) = N'[
    {"sourcePath": "$.employeeInfo.age", "targetKey": "employeeInfo.age"},
    {"sourcePath": "$.employeeInfo.gender", "targetKey": "employeeInfo.gender"},
    {"sourcePath": "$.product.plan", "targetKey": "product.plan"}
]';

DECLARE @sql NVARCHAR(MAX);

-- 生成FOR JSON PATH需要的列定义（将嵌套键的.替换为_，避免SQL列名冲突）
SELECT @sql = STRING_AGG(
    CONCAT(
        'JSON_VALUE(info, ''', sourcePath, ''') AS ', 
        QUOTENAME(REPLACE(targetKey, '.', '_'))
    ),
    ', '
)
FROM OPENJSON(@fields)
WITH (
    sourcePath NVARCHAR(MAX) '$.sourcePath',
    targetKey NVARCHAR(MAX) '$.targetKey'
);

-- 拼接完整查询语句
SET @sql = CONCAT(
    N'SELECT ', @sql, '
    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, INCLUDE_NULL_VALUES;'
);

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

扩展：支持提取完整子对象 #

如果需要提取整个子对象（比如完整的product），可以在字段映射中加入类型标记，用JSON_QUERY提取：

DECLARE @fields NVARCHAR(MAX) = N'[
    {"sourcePath": "$.employeeInfo.age", "targetKey": "employeeInfo.age", "type": "value"},
    {"sourcePath": "$.employeeInfo.gender", "targetKey": "employeeInfo.gender", "type": "value"},
    {"sourcePath": "$.product", "targetKey": "product", "type": "query"}
]';

DECLARE @sql NVARCHAR(MAX);

SELECT @sql = STRING_AGG(
    CASE type
        WHEN 'value' THEN CONCAT(
            'JSON_VALUE(info, ''', sourcePath, ''') AS ', 
            QUOTENAME(REPLACE(targetKey, '.', '_'))
        )
        WHEN 'query' THEN CONCAT(
            'JSON_QUERY(info, ''', sourcePath, ''') AS ', 
            QUOTENAME(targetKey)
        )
    END,
    ', '
)
FROM OPENJSON(@fields)
WITH (
    sourcePath NVARCHAR(MAX) '$.sourcePath',
    targetKey NVARCHAR(MAX) '$.targetKey',
    type NVARCHAR(10) '$.type'
);

SET @sql = CONCAT(
    N'SELECT ', @sql, '
    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, INCLUDE_NULL_VALUES;'
);

EXEC sp_executesql @sql;

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