Node.js场景下基于单条MySQL查询获取列去重值数组与聚合统计结果
单条SQL直接输出目标JSON结构的实现方案
MySQL 5.7及以上版本自带原生JSON构造函数,完全可以在SQL层完成所有去重、聚合、结构组装的工作,不需要拿到应用层再做二次拼接,单条查询就能输出完全匹配要求的字典结构,不需要多次查询。
原有查询方案的问题根因
- 直接执行
SELECT DISTINCT year FROM table_name时,数据库返回的是标准行集结构,每行的列值都会被驱动程序映射为「列名:列值」的键值对对象,最终拿到的就是一堆携带year键的对象数组,而非纯数值数组 - 换用
GROUP_CONCAT(DISTINCT year)时,返回的是逗号拼接的纯字符串,既不是数组类型,拿到应用层还要做字符串切分、数值类型转换,存在不必要的冗余开发成本
最优实现SQL
利用全表聚合+原生JSON构造函数,一次全表扫描就能完成所有计算,不需要嵌套子查询,核心用到JSON_OBJECT(构造嵌套键值对对象)、JSON_ARRAYAGG(构造纯值数组)两个函数:
SELECT JSON_OBJECT( -- 组装years字段:直接生成去重后的年份数值数组 'years', JSON_ARRAYAGG(DISTINCT `year`), -- 组装int_colors字段:直接生成去重后的颜色字符串数组 'int_colors', JSON_ARRAYAGG(DISTINCT `color`), -- 组装min-date字段:取表中最早的日期值 'min-date', MIN(`date_column`), -- 组装嵌套的prices字段 'prices', JSON_OBJECT( 'min', MIN(`price`), 'max', MAX(`price`) ) ) AS target_result FROM table_name;
注:SQL中的
color、date_column、price需要替换为表中对应颜色、日期、价格字段的实际列名
输出结果说明
执行上述查询后,数据库只会返回1行1列的结果,驱动程序返回的行结构为:
[ { "target_result": { "years": [2011,2012,2013,2014], "int_colors": ["red","green"], "min-date": "2020-11-01", "prices": { "min": 0, "max": 500 } } } ]
只要取出target_result字段的对应值,就是完全符合要求的字典结构,不需要做任何额外的拆分、类型转换操作。
注意事项
- 版本兼容:需要MySQL 5.7及以上版本,早期版本不支持原生JSON构造函数
- 长度限制规避:JSON_ARRAYAGG构造的是原生JSON类型,不受
group_concat_max_len的默认长度截断限制,全表去重值较多时也不会出现内容丢失 - 空值处理:如果对应列存在null值,可在JSON_ARRAYAGG外层套
JSON_REMOVE过滤数组中的空值,例如:JSON_REMOVE(JSON_ARRAYAGG(DISTINCT `year`), '$[?(@ == null)]')
内容的提问来源于stack exchange,提问作者Kavindu Pasan Kavithilaka
相关产品推荐
相关产品推荐

