SQL Server 2016：查询JSON列缺失指定字段的Shipments记录

由于SQL Server 2016支持OPENJSON函数解析JSON数据，我们可以通过嵌套展开JSON数组的方式筛选目标记录：

方法1：使用CROSS APPLY展开数组并去重 #

SELECT DISTINCT s.shipment_number, s.shipment_json
FROM Shipments s
CROSS APPLY OPENJSON(s.shipment_json, '$.stops') AS stops
CROSS APPLY OPENJSON(stops.value, '$.locations') 
WITH (
    stopRole varchar(50) '$.stopRole',
    closeDateTime datetime2 '$.closeDateTime'
) AS locations
WHERE locations.stopRole = 'destinationPort' 
AND locations.closeDateTime IS NULL;

方法2：使用EXISTS子查询（避免重复记录） #

SELECT s.shipment_number, s.shipment_json
FROM Shipments s
WHERE EXISTS (
    SELECT 1
    FROM OPENJSON(s.shipment_json, '$.stops') AS stops
    CROSS APPLY OPENJSON(stops.value, '$.locations') 
    WITH (
        stopRole varchar(50) '$.stopRole',
        closeDateTime datetime2 '$.closeDateTime'
    ) AS locations
    WHERE locations.stopRole = 'destinationPort' 
    AND locations.closeDateTime IS NULL
);

代码说明 #

• 第一次CROSS APPLY OPENJSON用于展开JSON中的stops数组；
• 第二次CROSS APPLY OPENJSON用于展开每个stop下的locations数组，并通过WITH子句映射出stopRole和closeDateTime字段；
• 当locations元素中缺失closeDateTime字段时，OPENJSON会将该字段值解析为NULL，因此通过closeDateTime IS NULL即可筛选出符合条件的记录；
• 方法2使用EXISTS子查询可以避免返回重复的原表记录，无需额外使用DISTINCT。

内容的提问来源于stack exchange，提问作者Eduardo Álvarez