如何修复SQL Server 2016+中多JSON键AND查询无结果的问题

问题原因 #

OPENJSON会将JSON列中的每个键值对拆分为单独的行，同一行记录不可能同时包含key='Name'和key='Guid'，因此直接在WHERE子句中用AND连接两个键值条件时，永远不会有匹配的行，导致无结果返回。

修复方案 #

方案1：使用多个EXISTS子查询分别验证每个键值对

通过两个独立的EXISTS，分别检查JSON中是否存在符合条件的Name和Guid键值对，只有当两个条件都满足时才返回记录：

SELECT * 
FROM [Logs] t
WHERE EXISTS (  
   SELECT 1
   FROM OPENJSON(t.[Parameters])
   WHERE [key] = 'Name' AND [value] = 'Test'
)
AND EXISTS (  
   SELECT 1
   FROM OPENJSON(t.[Parameters])
   WHERE [key] = 'Guid' AND [value] = '37aaecc3-e2f9-4b39-afcf-ba720c4e500a'
)

方案2：统计匹配的唯一键数量

利用COUNT(DISTINCT [key])统计满足条件的键的数量，确保两个目标键都匹配：

SELECT * 
FROM [Logs] t
WHERE EXISTS (  
   SELECT 1
   FROM OPENJSON(t.[Parameters])
   WHERE ([key] = 'Name' AND [value] = 'Test') 
      OR ([key] = 'Guid' AND [value] = '37aaecc3-e2f9-4b39-afcf-ba720c4e500a')
   HAVING COUNT(DISTINCT [key]) = 2
)

方案3：直接使用JSON_VALUE（SQL Server 2016+支持）

SQL Server 2016及以上版本本身支持JSON_VALUE函数，写法更简洁，无需依赖OPENJSON，效果和2022版本的逻辑一致（可通过JSON_VALUE(...) IS NOT NULL替代JSON_PATH_EXISTS检查键是否存在）：

SELECT *
FROM Logs
WHERE
    JSON_VALUE([Parameters], '$.Name') IS NOT NULL AND
    JSON_VALUE([Parameters], '$.Guid') IS NOT NULL AND
    JSON_VALUE([Parameters], '$.Name') = 'Test' AND
    JSON_VALUE([Parameters], '$.Guid') = '37aaecc3-e2f9-4b39-afcf-ba720c4e500a';

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