SQL Server OpenJson - retrieve row based on nested Json, quering multiple Json rows-CodePudding

Given the data table below, how can I retrieve only the row #3, querying the field "chave", based on multiple json rows?

I want to retrieve the master row where the json field (NomeCampo = id and Valor = 3) and also (NomeCampo = id2 and Valor = 5)

id  id_modulo   chave
624D4FB5-6197-11EA-A947-9C5C8ED7177E    17  [{"NomeCampo":"id","Valor":2},{"NomeCampo":"id2","Valor":5}]
4CF95795-4BFD-EC11-8CE5-80A589B639E0    17  [{"NomeCampo":"id","Valor":3},{"NomeCampo":"id2","Valor":4}]
DBE9275A-9BFF-EC11-8CE5-80A589B639E0    17  [{"NomeCampo":"id","Valor":3},{"NomeCampo":"id2","Valor":5}]
BE3228C6-9BFF-EC11-8CE5-80A589B639E0    17  [{"NomeCampo":"id","Valor":3},{"NomeCampo":"id2","Valor":6}]

This is the SQL that I have but it is not retrieving any row at all:

SELECT id, id_modulo, chave
FROM myTable
WHERE  
id_modulo = 17
and

EXISTS (
   SELECT *
   FROM OPENJSON(chave) WITH (
     NomeCampo nvarchar(max) '$.NomeCampo', 
     Valor nvarchar(max) '$.Valor'

   ) AS [Info]
    WHERE 
    ([Info].NomeCampo = 'id' and [Info].Valor= '3') and
     ([Info].NomeCampo = 'id2' and [Info].Valor= '5')
)

Is this even possible to do?

CodePudding user response：

This is actually a case of relational division. You need to find the set of JSON rows which have these properties, so you need to group it

SELECT
  t.id,
  t.id_modulo,
  t.chave
FROM myTable t
WHERE t.id_modulo = 17
AND EXISTS (SELECT 1
    FROM OPENJSON(t.chave) WITH (
      NomeCampo nvarchar(100),
      Valor nvarchar(1000)
    ) AS Info
    WHERE (Info.NomeCampo = 'id'  AND Info.Valor = '3'
        OR Info.NomeCampo = 'id2' AND Info.Valor = '5')
    HAVING COUNT(*) = 2
);

Another option

SELECT
  t.id,
  t.id_modulo,
  t.chave
FROM myTable t
WHERE t.id_modulo = 17
AND EXISTS (SELECT 1
    FROM OPENJSON(t.chave) WITH (
      NomeCampo nvarchar(100),
      Valor nvarchar(1000)
    ) AS Info
    HAVING COUNT(CASE WHEN Info.NomeCampo = 'id'  AND Info.Valor = '3' THEN 1 END) > 0
       AND COUNT(CASE WHEN Info.NomeCampo = 'id2' AND Info.Valor = '5' THEN 1 END) > 0
);