Laravel whereJsonContains在whereExists子查询中无结果的解决问询

无结果的查询（Query1） #

当前执行无结果的代码：

// 获取所有有关联的产品分类
$product_categories = ProductCategory::whereExists(function($query) {
        $query->select('id')
            ->from('restaurants')
            ->whereJsonContains('restaurants.restaurant_categories', 'product_categories.name');
})->get();

// 日志
dd($product_categories->toSql());

SQL查询输出：

select *
from `product_categories`
where exists (
    select `id`
    from `restaurants`
    where json_contains(`restaurants`.`restaurant_categories`, ?)
)
and `product_categories`.`deleted_at` is null

有结果的查询（Query2） #

执行后有结果的代码：

// 获取所有有关联的产品分类
$product_categories = ProductCategory::whereExists(function($query) {
        $query->select('id')
            ->from('restaurants')
            ->whereJsonContains('restaurants.restaurant_categories', 'Food');
})->get();

// 日志
dd($product_categories->toSql());

SQL查询输出：

select *
from `product_categories`
where exists (
    select `id`
    from `restaurants`
    where json_contains(`restaurants`.`restaurant_categories`, ?)
)
and `product_categories`.`deleted_at` is null

观察结论 #

• 两个SQL输出结构一致
• 差异在于whereJsonContains的第二个参数
• Query1传入的是表字段product_categories.name
• Query2传入的是直接字符串值'Food'

问题 #

1. 如何让查询通过product_categories表的name字段值进行筛选（使Query1生效）？
2. 我遗漏了什么？

表结构上下文 #

表：restaurants #

表：product_categories #

仍无结果的更新查询（Query3） #

更新后执行仍无结果的代码：

// 获取所有有关联的产品分类
$product_categories = ProductCategory::whereExists(function($query) {
        $query->select('id')
            ->from('restaurants')
            ->whereJsonContains('restaurants.restaurant_categories', \DB::raw('product_categories.name'));
})->get();

// 日志
dd($product_categories->toSql());

Query3的SQL查询输出：

select *
from `product_categories`
where exists (
    select `id`
    from `restaurants`
    where json_contains(`restaurants`.`restaurant_categories`, product_categories.name)
)
and `product_categories`.`deleted_at` is null

解决方案 #

问题原因 #

Query3里直接用product_categories.name作为JSON_CONTAINS的参数，会被当作原始字符串处理，但restaurant_categories存储的是JSON数组，每个元素是带双引号的字符串（比如"Food"）。而product_categories.name的值是Food（不带引号），格式不匹配导致JSON_CONTAINS匹配失败。

正确写法 #

需要把product_categories.name的值转换成JSON格式的字符串，以下两种方法都可以实现：

方法1：使用JSON_QUOTE函数（推荐）

$product_categories = ProductCategory::whereExists(function($query) {
    $query->select('id')
        ->from('restaurants')
        ->whereJsonContains(
            'restaurants.restaurant_categories',
            \DB::raw('JSON_QUOTE(product_categories.name)')
        );
})->get();

对应的SQL：

select *
from `product_categories`
where exists (
    select `id`
    from `restaurants`
    where json_contains(`restaurants`.`restaurant_categories`, JSON_QUOTE(product_categories.name))
)
and `product_categories`.`deleted_at` is null

JSON_QUOTE会自动给字段值加上双引号，让它和JSON数组里的元素格式完全一致，确保匹配成功。

方法2：手动拼接引号

$product_categories = ProductCategory::whereExists(function($query) {
    $query->select('id')
        ->from('restaurants')
        ->whereJsonContains(
            'restaurants.restaurant_categories',
            \DB::raw('CONCAT(\'"\', product_categories.name, \'"\')')
        );
})->get();

通过CONCAT函数给字段值前后拼接双引号，效果和JSON_QUOTE一致，但JSON_QUOTE能更安全地处理值本身包含引号的场景。

你遗漏的点 #

• JSON数组中的元素是带双引号的字符串，而数据库字段product_categories.name的值是不带引号的普通字符串，两者格式不匹配，导致JSON_CONTAINS无法识别。
• 直接传入字段名时，必须确保它的格式和JSON字段里的元素格式一致，需要用JSON_QUOTE或字符串拼接完成格式转换。

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