MySQL 8.0中未知索引的JSON字段查询问题求助

问题原因 #

你之前的查询语句返回的是JSON数组（格式类似["值1", "值2", ...]），直接和单个字符串比较自然匹配不到。当指定具体索引[102]时，返回的是该位置的单个字符串值，所以能查到结果。

解决方案 #

以下是三种可行的查询方式，可根据实际需求选择：

1. 使用JSON_CONTAINS（最简洁的存在性检查） #

直接检查JSON数组中是否包含符合条件的元素：

SELECT * 
FROM vendas_pendentes 
WHERE JSON_CONTAINS(venda->'$.data.items', '{"order_reference": "171972786245566"}');

2. 使用JSON_SEARCH（可返回匹配路径） #

通过判断值的搜索路径是否存在来筛选数据：

SELECT * 
FROM vendas_pendentes 
WHERE JSON_SEARCH(venda, 'one', '171972786245566', NULL, '$.data.items[*].order_reference') IS NOT NULL;

• 'one'表示只查找第一个匹配项，换成'all'可返回所有匹配的路径
• 最后一个参数用于指定搜索的JSON路径模板

3. 使用JSON_TABLE（适合需要展开数组的场景） #

将JSON数组转换成临时表后关联查询，适合需要同时获取数组元素详情的场景：

SELECT vp.*
FROM vendas_pendentes vp
JOIN JSON_TABLE(
    vp.venda->'$.data.items',
    '$[*]' COLUMNS(
        order_reference VARCHAR(255) PATH '$.order_reference'
    )
) jt
WHERE jt.order_reference = '171972786245566';

补充优化建议 #

如果需要频繁查询该JSON字段的内容，可考虑添加生成列和索引来提升性能：

-- 添加生成列存储所有order_reference值
ALTER TABLE vendas_pendentes 
ADD COLUMN order_references JSON GENERATED ALWAYS AS (venda->'$.data.items[*].order_reference') STORED;

-- 针对数组类型创建索引
CREATE INDEX idx_order_references ON vendas_pendentes((CAST(order_references AS UNSIGNED ARRAY)));

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