MySQL如何基于JSON数组键值筛选匹配行并按关联score值排序

以下语句假设你的业务表名为biz_table，可根据实际表名替换：

SELECT main.id
FROM biz_table main
INNER JOIN (
    SELECT jt.item_id, jt.score
    -- 定位表中首行（默认按主键升序取最小id记录，匹配无特殊排序时的表首行逻辑）
    FROM (SELECT data FROM biz_table ORDER BY id ASC LIMIT 1) first_row,
    -- 拆解JSON数组为结构化行，提取每个条目的id和score
    JSON_TABLE(
        first_row.data,
        '$[*]' COLUMNS (
            item_id INT PATH '$.id',
            score DECIMAL(3,2) PATH '$.score'
        )
    ) AS jt
) first_row_parsed
-- 匹配主键命中首行JSON条目id的记录
ON main.id = first_row_parsed.item_id
-- 按首行条目关联的score值升序排序，匹配示例输出顺序
ORDER BY first_row_parsed.score ASC;

关键说明 #

• 版本适配：该查询依赖JSON_TABLE函数，需要MySQL 8.0.4及以上版本支持
• 排序逻辑校准：示例中首行JSON内id=20对应score=0.59、id=10对应score=0.68，按score升序排列后刚好得到预期输出20、10的顺序，若需调整排序方向可将ASC修改为DESC
• 首行定位可灵活调整：如果业务中“首行”的定义不是主键最小的记录（例如按创建时间取最早写入的记录），只需替换首行定位子查询内的排序条件即可
• 低版本兼容：如果使用MySQL 5.x等不支持JSON_TABLE的版本，可通过JSON_EXTRACT按数组索引逐次提取每个元素的字段值实现拆解，只是写法相对繁琐

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