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

Question

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?

Please do not post images of code or data, please paste them in as text — Charlieface, Jul 10 '22 at 11:43

Charlieface · Accepted Answer · 2022-07-11T13:53:44.910

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')
    GROUP BY ()
    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
    GROUP BY ()
    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
);

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

1 Answers1