MySQL/MariaDB中按cold_storage_lot_number查询JSON数组失败问题

存储在inventory表cold_storage_data列的是JSON数组，不是单个JSON对象，这是你之前查询失败的核心原因。另外MariaDB 10.3的JSON函数行为和MySQL 8存在差异，以下是问题分析和可行方案：

错误原因 #

你之前的查询都是针对单个JSON对象的路径（$.cold_storage_lot_number），但实际存储的是数组，这个路径会返回NULL，自然匹配不到数据。

可行查询方案 #

方案1：使用JSON_CONTAINS指定数组元素路径

SELECT *
FROM test.inventories
WHERE JSON_CONTAINS(cold_storage_data, '"678679"', '$[*].cold_storage_lot_number');

这里$[*].cold_storage_lot_number表示遍历数组中所有元素的cold_storage_lot_number属性，检查是否包含目标值。注意目标值需要用JSON字符串格式（带双引号）。

方案2：用JSON_TABLE展开数组后查询

将JSON数组展开为临时表，再关联查询：

SELECT i.*
FROM test.inventories i
JOIN JSON_TABLE(
    i.cold_storage_data,
    '$[*]' COLUMNS (
        lot_number VARCHAR(20) PATH '$.cold_storage_lot_number'
    )
) AS j ON j.lot_number = '678679';

这种方式更灵活，适合需要提取数组中其他属性的场景。

方案3：使用JSON_SEARCH判断是否存在匹配值

SELECT *
FROM test.inventories
WHERE JSON_SEARCH(cold_storage_data, 'one', '678679', NULL, '$[*].cold_storage_lot_number') IS NOT NULL;

JSON_SEARCH会返回匹配值的路径，若存在则结果不为NULL。

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