MySQL 8中如何提取并展开JSON对象内的数组并对元素去重？

你可以使用MySQL 8.0版本提供的JSON_TABLE函数实现类似Couchbase中UNNEST的效果，把每行存储的subscribersIds数组拆分为单值行，再进行去重处理。

1. 获取去重后的ID行结果 #

如果你需要的是每行一个独立ID的结果集，直接执行以下SQL：

SELECT DISTINCT subscriber_id
FROM customers,
     JSON_TABLE(
         doc -> '$.subscribersIds',
         '$[*]' COLUMNS (subscriber_id INT PATH '$')
     ) AS jt;

2. 直接获取逗号拼接的字符串结果 #

如果你需要直接得到170968, 225647, 228195格式的拼接字符串，可以搭配GROUP_CONCAT实现：

SELECT GROUP_CONCAT(DISTINCT subscriber_id SEPARATOR ', ') AS distinct_subscriber_ids
FROM customers,
     JSON_TABLE(
         doc -> '$.subscribersIds',
         '$[*]' COLUMNS (subscriber_id INT PATH '$')
     ) AS jt;

补充说明 #

• JSON_TABLE函数会将JSON数组中的每个元素映射为单独的一行，和原表做笛卡尔积即可实现数组扁平化效果
• 如果部分行的subscribersIds字段可能为空/无效JSON，可以在WHERE子句中添加过滤条件：WHERE JSON_VALID(doc -> '$.subscribersIds') AND JSON_LENGTH(doc -> '$.subscribersIds') > 0
• 拼接字符串如果长度超出默认限制，可以提前调整group_concat_max_len参数值。

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