Появилась потребность сохранять в БД последовательность событий, происходящих на сайте одного из проектов. В виду того, что события могут происходить сильно разные, параметры этих событий записываются как одно JSON поле. С разной структурой. Данных много, приходят по нескольку десятков событий каждую секунду.
Результат:
Дошло дело до аналитики, и потребовалось создать выборку, где необходимо было подсчитать для каждой строки во вложенном поле JSON количество элементов массива. Stackoverflow забит записями о том, как выбрать из единичного поле но сложной структуры, но при этом нет примеров, о том, как посчитать для многих строк.
Первой итерацией стало получение из единичной строки, как указано здесь
Результат:
Т.е. достаточно было совместить основной запроc с OpenJson. Однако, при выполнении Cross Apply запрос пытался получить данные о длинах массивов по большому числу строк и уходил в длительную обработку.
Добавил подзапросом сперва получение последних по времени 10 строк, а по ним уже выполнил CROSS APPLY с группировкой
Результат:
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)
Надеюсь, кому-нибудь поможет :)
