如何查询JSON数组中匹配属性或值的对象本身的路径

方案1：使用REGEXP_REPLACE处理JSON_SEARCH返回结果（实现最简单） #

JSON_SEARCH返回的属性路径和你需要的对象路径只差末尾的.class后缀，直接用正则替换即可得到目标路径，示例语句：

SELECT REGEXP_REPLACE(
    JSON_SEARCH(json_col, 'one', 'Identity'),
    '\\.class$',
    ''
) AS object_path
FROM your_table;

上述语句会将$[0].class这类结果直接处理为$[0]。如果需要直接获取对应对象，可以嵌套JSON_EXTRACT：

SELECT JSON_EXTRACT(
    json_col,
    REGEXP_REPLACE(JSON_SEARCH(json_col, 'one', 'Identity'), '\\.class$', '')
) AS identity_object
FROM your_table;

注意：JSON_SEARCH参数顺序为JSON_SEARCH(待查询的JSON文档, 匹配模式one/all, 搜索值)，不要把JSON文档和搜索参数写反。

方案2：使用JSON_TABLE遍历数组（MySQL 8.0及以上支持，扩展性更强） #

如果需要处理多匹配场景、或者需要同时提取对象内的其他字段，可以用JSON_TABLE把数组拆成数据行遍历，直接获取下标和对应对象：

SELECT 
    CONCAT('$[', idx-1, ']') AS object_path,
    json_object AS identity_object
FROM your_table,
JSON_TABLE(
    your_table.json_col,
    '$[*]' COLUMNS (
        idx FOR ORDINALITY,
        class VARCHAR(32) PATH '$.class',
        json_object JSON PATH '$'
    )
) AS jt
WHERE jt.class = 'Identity';

说明：idx FOR ORDINALITY会自动生成从1开始的元素序号，减1后拼接即可得到符合JSON路径规范的数组下标。

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