Подсчет количества значений массива json в строке записи MS SQL

Появилась потребность сохранять в БД последовательность событий, происходящих на сайте одного из проектов. В виду того, что события могут происходить сильно разные, параметры этих событий записываются как одно JSON поле. С разной структурой. Данных много, приходят по нескольку десятков событий каждую секунду.

 SELECT TOP (10) [Id]
 ,[EventStart]
 ,[EventEnd]
 ,DATEDIFF(ms, eventstart, eventend) ms
 ,[DataJson]
 ,[RequestType]
FROM [Audit]
where RoutePath = 'Object/getInfos' and DATEADD(minute, -1, GETDATE()) < EventStart
order by id desc

Результат:

Id         EventStart                  EventEnd                    ms   DataJson                                               RequestType
---------- --------------------------- --------------------------- ---- ------------------------------------------------------ -------------------------
69095982   2018-09-19 13:06:29.2800000 2018-09-19 13:06:29.2966667 16   {"EventType":"Object/getInfos","Environment":{"UserNam POST
69095981   2018-09-19 13:06:29.2800000 2018-09-19 13:06:29.2966667 16   {"EventType":"Object/getInfos","Environment":{"UserNam POST
69095980   2018-09-19 13:06:29.2333333 2018-09-19 13:06:29.2800000 47   {"EventType":"Object/getInfos","Environment":{"UserNam POST
69095979   2018-09-19 13:06:29.0766667 2018-09-19 13:06:29.0933333 17   {"EventType":"Object/getInfos","Environment":{"UserNam POST
69095978   2018-09-19 13:06:29.0633333 2018-09-19 13:06:29.0766667 13   {"EventType":"Object/getInfos","Environment":{"UserNam POST
69095976   2018-09-19 13:06:28.8266667 2018-09-19 13:06:28.8733333 47   {"EventType":"Object/getInfos","Environment":{"UserNam POST
69095975   2018-09-19 13:06:28.4833333 2018-09-19 13:06:28.8133333 330  {"EventType":"Object/getInfos","Environment":{"UserNam POST
69095974   2018-09-19 13:06:28.6400000 2018-09-19 13:06:28.6566667 16   {"EventType":"Object/getInfos","Environment":{"UserNam POST
69095973   2018-09-19 13:06:28.5000000 2018-09-19 13:06:28.5766667 76   {"EventType":"Object/getInfos","Environment":{"UserNam POST
69095970   2018-09-19 13:06:28.1733333 2018-09-19 13:06:28.1866667 13   {"EventType":"Object/getInfos","Environment":{"UserNam POST

(10 rows affected)

Дошло дело до аналитики, и потребовалось создать выборку, где необходимо было подсчитать для каждой строки во вложенном поле JSON количество элементов массива. Stackoverflow забит записями о том, как выбрать из единичного поле но сложной структуры, но при этом нет примеров, о том, как посчитать для многих строк.

Первой итерацией стало получение из единичной строки, как указано здесь

declare @s varchar(8000)
set @s = '{ "EventType": "Object/getInfos", "Environment": { "MachineName": "MASTER", "DomainName": "IIS APPPOOL", "CallingMethodName": "Web2.Controllers.ObjectController.GetInfos()", "AssemblyName": "Web2, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null", "Culture": "ru-RU" }, "StartDate": "2018-09-18T15:06:23.7361083+03:00", "EndDate": "2018-09-18T15:06:24.0485875+03:00", "Duration": 312, "Action": { "HttpMethod": "POST", "ControllerName": "Object", "ActionName": "getInfos", "ActionParameters": { "ids": [320, 292, 299, 289, 8022], "force": false }, "UserName": "a.user", "RequestUrl": "/object/getInfos", "ResponseStatus": "200 OK", "ResponseStatusCode": 200 } }'
select *
from Openjson(@s, '$.Action.ActionParameters.ids') oj

Результат:

key        value    type
---------- -------- ----
0          320      2
1          292      2
2          299      2
3          289      2
4          8022     2
(5 rows affected)

Т.е. достаточно было совместить основной запроc с OpenJson. Однако, при выполнении Cross Apply запрос пытался получить данные о длинах массивов по большому числу строк и уходил в длительную обработку.

Добавил подзапросом сперва получение последних по времени 10 строк, а по ним уже выполнил CROSS APPLY с группировкой

select 
 c1.id
 ,c1.ms
 ,count(x.value) cnt
from(
   SELECT TOP (10) [Id]
    ,[EventStart]
    ,[EventEnd]
    ,DATEDIFF(ms, eventstart, eventend) ms
    ,[DataJson]
   FROM [Audit]
   where RoutePath = 'Object/getInfos' and DATEADD(minute, -1, GETDATE()) < EventStart
   order by id desc
   ) c1
CROSS APPLY OPENJSON(JSON_QUERY(c1.DataJson, '$.Action.ActionParameters.ids')) AS x
group by c1.id, c1.ms
order by count(x.value) desc

Результат:

id                   ms          cnt
-------------------- ----------- -----------
69110646             63          87
69110659             60          72
69110653             360         45
69110644             17          22
69110651             17          13
69110650             13          9
69110658             16          7
69110652             17          7
69110655             326         6
69110645             296         1

(10 rows affected)

Надеюсь, кому-нибудь поможет :)