ClickHouse中如何用visitParamExtractString获取重复key的第N个匹配值？

问题说明 #

使用visitParamExtractString按key提取JSON值时，若存在多个同名key，函数仅返回第一个匹配项。现有以下场景：

• 原查询语句：

SELECT time, visitParamExtractString(message, 'interval') as interval
FROM stage.logs dl 
WHERE time >= '2022-09-29 23:00:00'
order by time 

• 原始JSON数据：

{"body":[{"value":3,"interval":"SECOND","intervalNum":10},{"value":205015,"interval":"DAY","intervalNum":1}],"code":200,"endpoint":"/api/v3/rateLimit/order"}

需求为获取第二个interval字段的值DAY，而非默认返回的第一个值SECOND。

解决方案 #

方法1：直接通过JSON路径定位目标字段 #

visitParamExtractString支持JSON路径语法，可直接定位到数组中指定元素的目标字段（数组索引从0开始，第二个元素对应索引1）：

SELECT 
    time, 
    visitParamExtractString(message, 'body[1].interval') as interval
FROM stage.logs dl 
WHERE time >= '2022-09-29 23:00:00'
ORDER BY time

方法2：拆分数组提取与字段解析 #

若需更灵活处理数组元素，可分步解析：

SELECT 
    time, 
    visitParamExtractString(
        arrayElement(visitParamExtractArrayRaw(message, 'body'), 2), 
        'interval'
    ) as interval
FROM stage.logs dl 
WHERE time >= '2022-09-29 23:00:00'
ORDER BY time

• visitParamExtractArrayRaw：提取body对应的JSON数组字符串
• arrayElement：选取数组的第2个元素（ClickHouse中该函数索引从1开始）
• visitParamExtractString：从选中元素中提取interval值

预期结果 #

执行上述查询后将得到：

time                      interval
2022-10-30 20:02:01.333   DAY

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