Laravel如何实现带比较运算符的JSON数组列whereJsonContains查询

方法1：MySQL 8.0+ 推荐写法（最准确） #

直接使用JSON_TABLE将JSON数组转为临时关系表后做条件判断，Laravel中写法如下：

User::whereRaw('EXISTS (
    SELECT 1 FROM JSON_TABLE(
        experience,
        "$[*]" COLUMNS(
            years INT PATH "$.years"
        )
    ) AS exp_items WHERE exp_items.years >= 2
)')->get();

这种写法可以完美匹配所有数组内存在years大于等于2的记录，支持各种边界情况（比如years是两位数、三位数的情况）。

方法2：兼容MySQL 5.7 写法 #

如果你的MySQL版本低于8.0不支持JSON_TABLE，可以用JSON长度遍历的方式实现：

User::whereRaw('EXISTS (
    SELECT 1 FROM information_schema.numbers 
    WHERE numbers.n < JSON_LENGTH(experience)
    AND JSON_UNQUOTE(JSON_EXTRACT(experience, CONCAT("$[", numbers.n, "].years"))) >= 2
)')->get();

注意：该方法依赖information_schema.numbers系统表的序列值，默认可以支持最长1024长度的JSON数组，足够大部分业务场景使用。

可选快速写法（局限性高） #

如果你的years字段都是正整数，也可以用正则匹配提取的years数组：

User::whereRaw('JSON_EXTRACT(experience, "$[*].years") REGEXP "\\b([2-9]|[1-9][0-9]+)\\b"')->get();

局限性：仅支持正整数，不支持小数、负数场景，容易出现误匹配，非特殊情况不推荐使用。

之前写法失效的原因 #

• whereJsonContains仅支持全等匹配，本身不支持>=这类比较运算符，传入运算符的写法不符合方法设计逻辑
• experience->years的箭头语法是用来访问JSON对象的顶层属性的，你的experience是数组类型，直接访问years属性不存在，自然返回不了正确结果

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