SQL Server 2016如何从JSON提取整数数组并用于更新操作

嘿，我来帮你搞定这个SQL Server处理JSON数组的问题！你遇到的空值问题，根源是没告诉OPENJSON要把Markets当成JSON结构来处理，下面一步步给你讲清楚：

为什么之前的查询返回空值？ #

当你执行select j.Markets from openjson(@JSON) with(Markets nvarchar(max)) j时，SQL Server默认会尝试提取Markets对应的标量值（比如字符串、数字这种单个值），但Markets是一个数组（类型4确实代表数组，不是对象哦），不是标量，所以自然返回NULL。你需要加上AS JSON关键字，让SQL Server保留这个数组的JSON结构，这样才能继续解析。

正确提取Market ID的方法 #

这里有两种常用的方式：

方法1：先提取数组，再解析元素 #

DECLARE @JSON nvarchar(max) = N'{"Comments": "test", "Markets": [3, 151]}';

-- 提取所有Market ID
SELECT value AS MarketID
FROM OPENJSON(@JSON)
WITH (Markets nvarchar(max) AS JSON) j
CROSS APPLY OPENJSON(j.Markets);

这个方法先把Markets数组以JSON字符串的形式提取出来，再用CROSS APPLY配合OPENJSON拆分成单个的ID值（value字段就是数组里的每个元素）。

方法2：直接指定数组元素的类型 #

如果已经知道数组里的ID是整数，可以直接在嵌套的OPENJSON里指定类型，让结果更清晰：

DECLARE @JSON nvarchar(max) = N'{"Comments": "test", "Markets": [3, 151]}';

SELECT MarketID
FROM OPENJSON(@JSON)
WITH (
    Comments nvarchar(100), -- 顺便提取其他字段
    Markets JSON -- 用JSON类型或者nvarchar(max) AS JSON都可以
) j
CROSS APPLY OPENJSON(j.Markets)
WITH (MarketID int '$'); -- '$'代表数组当前元素的值

用提取的ID更新Market表 #

接下来就是你要的更新操作，把提取到的ID和Market表关联起来就行：

DECLARE @JSON nvarchar(max) = N'{"Comments": "test", "Markets": [3, 151]}';

UPDATE M
SET M.IsActive = 1 -- 替换成你实际要更新的字段和值
FROM Market M
INNER JOIN (
    -- 这里用上面的方法提取ID
    SELECT value AS MarketID
    FROM OPENJSON(@JSON)
    WITH (Markets nvarchar(max) AS JSON) j
    CROSS APPLY OPENJSON(j.Markets)
) AS MarketIDs ON M.ID = MarketIDs.MarketID;

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