SQL Server查询时修改JSON列，将DatesToRemember转为数组的方法

可以用以下SQL语句实现需求，假设你的表名为YourTable，存储JSON的列名为JsonColumn：

SELECT 
    Name,
    JSON_QUERY(
        CASE 
            -- 判断DatesToRemember是否为字符串类型
            WHEN JSON_TYPE(Favouties, '$.DatesToRemember') = 'STRING' THEN
                -- 将字符串格式的日期转为数组格式
                JSON_MODIFY(Favouties, '$.DatesToRemember', JSON_QUERY('["' + JSON_VALUE(Favouties, '$.DatesToRemember') + '"]'))
            -- 如果已经是数组或其他类型，保持原结构
            ELSE Favouties
        END
    ) AS Favouties,
    gender
FROM YourTable
-- 拆分JSON数组为单行数据
CROSS APPLY OPENJSON(JsonColumn)
WITH (
    Name NVARCHAR(50) '$.Name',
    -- 保留Favouties的JSON结构，方便后续修改
    Favouties NVARCHAR(MAX) '$.Favouties' AS JSON,
    gender NVARCHAR(10) '$.gender'
)
-- 将处理后的数据重新组合为JSON数组
FOR JSON PATH, ROOT('')

关键逻辑说明 #

• OPENJSON + WITH子句：把存储的JSON数组拆分成独立的行，同时指定要提取的字段，其中Favouties通过AS JSON标记为JSON类型，避免被解析成普通字符串。
• JSON_TYPE函数：用于检测DatesToRemember的JSON类型，精准判断是否为字符串。
• JSON_MODIFY + JSON_QUERY：修改Favouties中的DatesToRemember字段，把字符串日期包装成数组；JSON_QUERY确保生成的数组被识别为JSON对象，而不是带引号的字符串。
• FOR JSON PATH, ROOT('')：将处理后的单行数据重新拼接成符合要求的JSON数组格式。

内容的提问来源于stack exchange，提问作者Jaka rebel