Find match item in JSON array

Question

I have a SQL table called 'Interactions' and a column called Events that holds an array of JSON data. I am looking for any row where the Events json has any array with @odata.type = #Sitecore.XConnect.Goal

[
   {
      "@odata.type":"#Sitecore.XConnect.Collection.Model.PageViewEvent",
      "CustomValues":[

      ],
      "DefinitionId":"9326cb1e-cec8-48f2-9a3e-91c7dbb2166c",
      "ItemId":"5ae02a76-ac59-4dbb-913f-3b2e51d92bae",
      "Id":"b067f72f-1d60-4773-a2e8-9d23770fea54",
      "Timestamp":"2019-11-11T17:18:31.2206225Z",
      "ItemLanguage":"en",
      "ItemVersion":1,
      "Url":"/renewal-confirmation",
      "SitecoreRenderingDevice":{
         "Id":"fe5d7fdf-89c0-4d99-9aa3-b5fbd009c9f3",
         "Name":"Default"
      }
   },
   {
      "@odata.type":"#Sitecore.XConnect.Goal",
      "CustomValues":[

      ],
      "DataKey":"/sitecore/content/Client/home/renewal-confirmation",
      "DefinitionId":"c0bc91b9-b5fc-4faa-bc12-3dba8bbae44f",
      "ItemId":"5ae02a76-ac59-4dbb-913f-3b2e51d92bae",
      "EngagementValue":100,
      "Id":"e7031fb4-ce57-459f-a9f1-2682748d3431",
      "ParentEventId":"b067f72f-1d60-4773-a2e8-9d23770fea54",
      "Timestamp":"2019-11-11T17:18:31.2518732Z"
   }
]

What I am currently trying is this, but no results

select *
from [Interactions] I
where exists
( 
  select * 
  from openjson(I.Events,'$."@odata.type"') 
  where value = '#Sitecore.XConnect.Goal'
)

If I use this, I can get any row where the 1st item in the array @odata.type = #Sitecore.XConnect.Goal. This works. But I want it from any item in the array.

SELECT *
FROM [Interactions]
WHERE JSON_VALUE([Events], '$[0]."@odata.type"') = '#Sitecore.XConnect.Goal'

Zhorov · Accepted Answer · 2019-11-13 17:32:56Z

One possible approach is to parse the JSON column using OPENJSON() with explicit schema (WITH clause with columns definitions). With this approach you can filter the Interactions table and get information from the JSON data.

Table:

CREATE TABLE Interactions (
   Events nvarchar(max)
)
INSERT INTO Interactions
   (Events)
VALUES 
   (N'[
   {
      "@odata.type":"#Sitecore.XConnect.Collection.Model.PageViewEvent",
      "CustomValues":[

      ],
      "DefinitionId":"9326cb1e-cec8-48f2-9a3e-91c7dbb2166c",
      "ItemId":"5ae02a76-ac59-4dbb-913f-3b2e51d92bae",
      "Id":"b067f72f-1d60-4773-a2e8-9d23770fea54",
      "Timestamp":"2019-11-11T17:18:31.2206225Z",
      "ItemLanguage":"en",
      "ItemVersion":1,
      "Url":"/renewal-confirmation",
      "SitecoreRenderingDevice":{
         "Id":"fe5d7fdf-89c0-4d99-9aa3-b5fbd009c9f3",
         "Name":"Default"
      }
   },
   {
      "@odata.type":"#Sitecore.XConnect.Goal",
      "CustomValues":[

      ],
      "DataKey":"/sitecore/content/Client/home/renewal-confirmation",
      "DefinitionId":"c0bc91b9-b5fc-4faa-bc12-3dba8bbae44f",
      "ItemId":"5ae02a76-ac59-4dbb-913f-3b2e51d92bae",
      "EngagementValue":100,
      "Id":"e7031fb4-ce57-459f-a9f1-2682748d3431",
      "ParentEventId":"b067f72f-1d60-4773-a2e8-9d23770fea54",
      "Timestamp":"2019-11-11T17:18:31.2518732Z"
   }
]')

Statement:

SELECT *
FROM Interactions i
CROSS APPLY OPENJSON(i.Events) WITH (
   ODataType nvarchar(100) '$."@odata.type"'
   -- and additional columns definitions 
) j
WHERE j.ODataType = '#Sitecore.XConnect.Goal'

Jeroen Mostert · Accepted Answer · 2019-11-13 15:22:04Z

1

Your original query is close but doesn't work because you can't directly pick out scalars with OPENJSON. You want something like this:

SELECT *
FROM [interactions]
WHERE EXISTS (
    SELECT 1
    FROM OPENJSON([events]) WITH (
        [@odata.type] NVARCHAR(MAX)
    )
    WHERE [@odata.type] = '#Sitecore.XConnect.Goal'
)

answered Nov 13, 2019 at 15:22

Jeroen Mostert

29k2 gold badges60 silver badges92 bronze badges

Collectives™ on Stack Overflow

Find match item in JSON array

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related