在Trino SQL中使用JSON_EXTRACT_SCALAR提取JSON并统计指定字段次数

为什么json_extract_scalar返回NULL？ #

你的alert字段是JSON数组类型，而json_extract_scalar只能提取单个JSON对象中的标量值，直接用json_extract_scalar(alert, '$.name')无法定位数组内的元素——因为数组的路径需要指定索引（比如$[0].name），但这种方式只能取数组中某一个位置的元素，没法遍历所有元素判断是否为x1，所以返回NULL。

实现按日期统计x1出现次数的SQL语句 #

以Presto/Trino语法为例，你需要先展开JSON数组，再过滤统计：

SELECT
    panel,
    COUNT(*) AS "name = x1",
    date
FROM
    your_table
CROSS JOIN UNNEST(json_parse(alert)) AS t(alert_item)
WHERE
    json_extract_scalar(alert_item, '$.name') = 'x1'
GROUP BY
    panel,
    date
ORDER BY
    date;

语句说明 #

1. json_parse(alert)：将字符串类型的alert字段解析为JSON数组（如果你的alert字段已经是JSON类型，可以省略这一步）。
2. CROSS JOIN UNNEST(...)：把JSON数组拆分成多行，每个数组元素单独成为一行记录，alert_item就是拆分后的单个JSON对象。
3. 过滤条件：用json_extract_scalar(alert_item, '$.name') = 'x1'筛选出name为x1的记录。
4. 分组统计：按panel和date分组，用COUNT(*)统计每组中x1的出现次数。

执行上述语句后，就能得到你期望的结果：

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