Scenarios

how to support do aggregation inside an event

select * from events;

[{"magtec":"[,{\"AssetID\":1933,\"Reason\":205},{\"AssetID\":1934,\"Reason\":2157},{\"AssetID\":1939,\"Reason\":2151},{\"AssetID\":1957,\"Reason\":205},{\"AssetID\":1964,\"Reason\":103},{\"AssetID\":2233,\"Reason\":68},{\"AssetID\":2234,\"Reason\":203},{\"AssetID\":2235,\"Reason\":203},{\"AssetID\":2259,\"Reason\":203},{\"AssetID\":2260,\"Reason\":2151}]"}]

How to support this ?

SELECT json_path_query(magtec, "$[*].AssetID") AS AssetID,json_path_query(magtec, "$[*].Reason") AS Reason, count(*) FROM events WHERE json_path_query(magtec, "$[*].AssetID")=1931 GROUP BY json_path_query(magtec, "$[*].AssetID"),json_path_query(magtec, "$[*].Reason");