如何在SQL中统计JSON列中含特定操作的会话数

给定包含session_id和json_column的表，json_column以键值对形式存储会话内的唯一事件名称与对应时间戳，需统计每个事件关联的会话数量。以下是不同主流数据库的实现语句：

PostgreSQL #

利用json_each_text（若存储为jsonb类型则用jsonb_each_text）将JSON键值对拆分为行，再分组统计：

SELECT 
    event_name AS "Event Name",
    COUNT(DISTINCT session_id) AS "# of sessions"
FROM 
    your_table,
    json_each_text(json_column) AS events(event_name, timestamp)
GROUP BY 
    event_name
ORDER BY 
    "# of sessions" DESC, event_name;

MySQL 8.0+ #

使用JSON_TABLE解析JSON的键列表，生成行数据后统计：

SELECT 
    event_name AS `Event Name`,
    COUNT(DISTINCT session_id) AS `# of sessions`
FROM 
    your_table,
    JSON_TABLE(
        JSON_KEYS(json_column),
        '$[*]' COLUMNS (event_name VARCHAR(255) PATH '$')
    ) AS events
GROUP BY 
    event_name
ORDER BY 
    `# of sessions` DESC, event_name;

SQL Server #

通过OPENJSON解析JSON对象，提取事件名称后分组统计：

SELECT 
    [key] AS [Event Name],
    COUNT(DISTINCT session_id) AS [# of sessions]
FROM 
    your_table
CROSS APPLY 
    OPENJSON(json_column)
GROUP BY 
    [key]
ORDER BY 
    [# of sessions] DESC, [key];

说明 #

所有查询均通过拆分JSON对象为行数据，再按事件名称分组，统计不同session_id的数量（因单个会话内同一事件仅出现一次，COUNT(DISTINCT)直接得到会话数），最终按会话数降序、事件名称排序输出。

内容的提问来源于stack exchange，提问作者dnarb