SQL查询JSON获取drawing-room分类行返回NULL求解决

你的SQL查询返回NULL是因为JSON路径引用错误，以下是修正后的方案：

错误原因分析 #

你通过JSON(item_details->>'discount') as object已经提取出了discount数组中的每个对象，此时object代表的是数组里的单个discount对象（比如包含target的那个对象），但你后续的路径object->>'discount[0].target.item[0].category'还在试图从object里找discount属性，这显然不存在，所以返回NULL。

修正后的查询方式 #

方式1：使用JSON_TABLE展开数组（推荐，适配多元素场景） #

如果discount或item数组可能包含多个元素，用JSON_TABLE展开后匹配更准确：

SELECT d.*
FROM DATA d,
     JSON_TABLE(
         d.item_details->>'$.discount',
         '$[*]' COLUMNS (
             target JSON PATH '$.target'
         )
     ) discount_items,
     JSON_TABLE(
         discount_items.target->>'$.item',
         '$[*]' COLUMNS (
             category VARCHAR(255) PATH '$.category'
         )
     ) item_details
WHERE item_details.category = 'drawing-room';

方式2：直接用JSON_EXISTS判断路径 #

如果只需要判断是否存在匹配的category，不需要展开数组，可以用JSON_EXISTS：

SELECT *
FROM DATA
WHERE JSON_EXISTS(
    item_details,
    '$.discount[*].target.item[*].category ? (@ == "drawing-room")'
);

方式3：简化路径的基础查询 #

如果确定discount和item数组只有第一个元素，也可以直接修正路径：

SELECT *
FROM DATA
WHERE item_details->>'$.discount[0].target.item[0].category' = 'drawing-room';

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