MariaDB中使用JSON_QUERY提取JSON数组多层对象失败的问题

问题原因 #

MariaDB的JSON路径语法不支持$.nodeDataArray.*.category这种直接通配数组元素的写法，这是它和部分在线JSON查询工具的语法差异。要提取数组中所有对象的category字段，需要用JSON_TABLE来展开数组再处理。

方法1：提取所有category值（行形式） #

用JSON_TABLE将JSON数组转换为关系型表结构，直接查询每个元素的category：

SELECT jt.category
FROM pr_models,
     JSON_TABLE(
         model_json,
         '$.nodeDataArray[*]' COLUMNS (
             category VARCHAR(255) PATH '$.category'
         )
     ) AS jt
WHERE model_id = 2;

执行后会把数组里每个对象的category作为单独的行返回。

方法2：将所有category值聚合为JSON数组 #

如果需要把所有category值重新组合成一个JSON数组返回，可结合JSON_ARRAYAGG：

SELECT JSON_ARRAYAGG(jt.category) AS all_categories
FROM pr_models,
     JSON_TABLE(
         model_json,
         '$.nodeDataArray[*]' COLUMNS (
             category VARCHAR(255) PATH '$.category'
         )
     ) AS jt
WHERE model_id = 2;

执行后会得到类似["cat1","cat2","cat3"]的结果。

注意事项 #

• JSON_TABLE是MariaDB 10.2.3及以上版本支持的功能，请确认你的数据库版本符合要求。
• 如果category是数字类型，可将VARCHAR(255)替换为对应数值类型（如INT）。

内容的提问来源于stack exchange，提问作者Alejandro Da Costa Urrutia