如何在MySQL的orders表JSON数组中提取master类型的id值？

针对你的需求，这里提供两种适用于不同MySQL版本的解决方案：

方法一：使用JSON_TABLE（MySQL 8.0+ 推荐） #

这种方式将JSON数组转换为关系型表结构，可读性更强，也便于后续筛选或关联操作：

SELECT jt.id AS master_id
FROM orders,
     JSON_TABLE(
         orders.ids,
         '$[*]' COLUMNS(
             type VARCHAR(20) PATH '$.type',
             id VARCHAR(20) PATH '$.id'
         )
     ) AS jt
WHERE jt.type = 'master';

方法二：使用JSON_EXTRACT + JSON_SEARCH（MySQL 5.7+ 兼容） #

通过JSON函数直接定位目标元素的路径，无需转换表结构：

SELECT JSON_UNQUOTE(
    JSON_EXTRACT(
        ids,
        CONCAT(
            '$[',
            SUBSTRING_INDEX(SUBSTRING_INDEX(JSON_UNQUOTE(JSON_SEARCH(ids, 'one', 'master', NULL, '$[*].type')), '[', -1), ']', 1),
            '].id'
        )
    )
) AS master_id
FROM orders;

步骤说明：

1. JSON_SEARCH(ids, 'one', 'master', NULL, '$[*].type')：查找数组中第一个type为master的元素路径，返回带双引号的结果（如"$[0]"）
2. JSON_UNQUOTE(...)：去除路径字符串的外层双引号，得到$[0]
3. SUBSTRING_INDEX(...)：从路径中提取出数组索引数字（如0）
4. CONCAT(...)：拼接成提取id的完整JSON路径（如$[0].id）
5. JSON_EXTRACT(...)：根据路径取出id值，最后用JSON_UNQUOTE去除结果的双引号

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