如何在SingleStore中对JSON列执行数组集合合并操作？

问题 #

需要实现两个目标：

1. 在SingleStore列式表中存储array[string]类型的数据
2. 在特定时间范围/条件下对该列执行集合合并并去重的操作

由于SingleStore不支持原生数组列，改用JSON列存储，但不知道如何用SingleStore SQL实现上述集合合并操作。

示例表 #

预期输出 #

其他数据库/框架实现方式 #

• Postgres：支持原生数组列，可通过array_agg(distinct elem)实现
• Spark：使用array_distinct(array_agg(col))即可实现

SingleStore实现方案 #

利用SingleStore的JSON处理函数，通过「拆分JSON数组→去重→重新聚合」的步骤实现需求：

具体SQL语句 #

SELECT
    key,
    JSON_ARRAYAGG(DISTINCT location ORDER BY location) AS merged_distinct_locations
FROM
    your_table_name,
    JSON_TABLE(locations, '$[*]' COLUMNS (location VARCHAR(255) PATH '$')) AS jt
-- 可添加时间范围/自定义条件过滤
-- WHERE your_time_column BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY
    key;

步骤解释 #

1. 拆分JSON数组：通过JSON_TABLE函数将每行的JSON数组拆分为多行单值数据，把数组元素转化为可单独处理的行记录。
2. 去重聚合：按key分组后，使用JSON_ARRAYAGG(DISTINCT location)将分组内的去重元素重新聚合为JSON数组，ORDER BY location用于指定数组内元素的排序（可根据需求移除）。

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