Laravel如何查询JSON列中数值处于两个指定值之间的记录

你之前使用的whereJsonContains确实仅支持精确值匹配，无法实现JSON数组内字段的区间查询，下面提供两种可行的实现方案：

方案1：JSON_TABLE 实现（MySQL 8.0+ 推荐） #

利用MySQL 8.0提供的JSON_TABLE函数，可以将JSON数组拆解为临时关系表，直接对拆解后的age字段做区间过滤，精度高、性能好，支持JSON索引优化。

原生SQL示例 #

SELECT * FROM `products`
WHERE EXISTS (
    SELECT 1 
    FROM JSON_TABLE(
        `viewers`,
        '$[*]' COLUMNS(
            age INT PATH '$.age'
        )
    ) AS viewer_json
    WHERE viewer_json.age BETWEEN 20 AND 30
);

对应Eloquent写法 #

$query->whereExists(function ($subQuery) {
    $subQuery->selectRaw('1')
        ->fromRaw('JSON_TABLE(`viewers`, \'$[*]\' COLUMNS(age INT PATH \'$.age\')) AS viewer_json')
        ->whereBetween('viewer_json.age', [20, 30]);
});

方案2：正则匹配实现（兼容低版本MySQL） #

正则方案可以实现需求，但性能和精度低于专门的JSON函数，仅适合低版本数据库或者临时查询场景。

正则规则说明 #

匹配JSON中"age":后紧跟20~30的数值，且数值后紧跟数组元素分隔符或对象闭合符，避免误匹配：
"age":\s*(2[0-9]|30)(,|\s*})

原生SQL示例 #

SELECT * FROM `products` 
WHERE `viewers` REGEXP '"age":\\s*(2[0-9]|30)(,|\\s*})';

对应Eloquent写法 #

$query->whereRaw('`viewers` REGEXP ?', ['"age":\s*(2[0-9]|30)(,|\s*})']);

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