SQL Server使用OPENJSON查询嵌套JSON多条件匹配行的方法

原SQL返回空结果是逻辑判断错误：OPENJSON(chave)会把chave存储的JSON数组拆分为独立的行，每行对应数组内的一个JSON对象。你在子查询中要求单条拆分后的记录同时满足NomeCampo = 'id'和NomeCampo = 'id2'，这个条件永远不可能成立——单条记录的NomeCampo只会有一个值，不可能同时等于两个不同字符串。

需求完全可以实现，以下两种常用写法都能准确返回目标的第3行记录：

写法1：双EXISTS校验 #

逻辑最直观，分别判断两个目标键值对是否存在，性能表现更好：

SELECT id, id_modulo, chave
FROM myTable
WHERE id_modulo = 17
  AND EXISTS (
      SELECT 1
      FROM OPENJSON(chave)
      WITH (
          NomeCampo nvarchar(100) '$.NomeCampo',
          Valor int '$.Valor'
      ) AS Info
      WHERE Info.NomeCampo = 'id' AND Info.Valor = 3
  )
  AND EXISTS (
      SELECT 1
      FROM OPENJSON(chave)
      WITH (
          NomeCampo nvarchar(100) '$.NomeCampo',
          Valor int '$.Valor'
      ) AS Info
      WHERE Info.NomeCampo = 'id2' AND Info.Valor = 5
  )

写法2：匹配计数校验 #

适合需要同时匹配多个键值对的场景，写法更简洁：拆分JSON后统计符合目标条件的记录数，计数等于条件总数就说明所有条件都满足。

SELECT id, id_modulo, chave
FROM myTable
WHERE id_modulo = 17
  AND (
      SELECT COUNT(*)
      FROM OPENJSON(chave)
      WITH (
          NomeCampo nvarchar(100) '$.NomeCampo',
          Valor int '$.Valor'
      ) AS Info
      WHERE (Info.NomeCampo = 'id' AND Info.Valor = 3)
         OR (Info.NomeCampo = 'id2' AND Info.Valor = 5)
  ) = 2

注意：原写法中将Valor字段映射为nvarchar(max)类型，实际JSON中存储的是数值类型，直接映射为int类型做比较可以避免隐式转换导致的匹配错误，查询效率也更高。

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