Laravel 8查询含对象数组的JSON字段返回0结果的正确方法咨询

你的查询返回空结果主要是以下几个错误导致的：

• 字段值匹配颠倒：你给出的示例数据中sku值为DTI-LALF3-EA18，asin值为B07V3SSLN11，但你写JSON_CONTAINS时把两个字段的对应值写反了，自然匹配不到数据
• JSON_CONTAINS使用逻辑错误：当查询字段是对象数组时，JSON_CONTAINS需要传入完整匹配的对象结构，或者指定匹配路径才能识别数组内的子对象
• 代码语法笔误：你写的->>、>whereRaw属于链式调用的语法错误，会导致SQL生成异常
• JSON_EXTRACT + whereIn组合错误：JSON_EXTRACT(info, '$[*].asin')返回的是JSON格式的数组，不是SQL可以直接匹配的普通值集合，所以whereIn无法生效

方案1：使用JSON_CONTAINS匹配数组内的子对象 #

如果要查询数组中存在至少一个对象同时满足sku=DTI-LALF3-EA18、asin=B07V3SSLN11，可以直接传入完整的匹配对象，修改后代码如下：

$initial_results = DB::table('toys')->select('id','name')->where(['name' => 'sammy', 'email' => 'whateveremail']);
if($sk === 'yes') {
    // 用参数绑定传JSON字符串避免转义错误，匹配数组内是否包含指定结构的子对象
    $results = $initial_results->whereRaw(
        'JSON_CONTAINS(`info`, ?)',
        [json_encode(['sku' => 'DTI-LALF3-EA18', 'asin' => 'B07V3SSLN11'])]
    )->get();
} else {
    $results = $initial_results->get();
}

如果只需要匹配单个字段，不需要同时满足两个字段条件，只需要修改json_encode里的数组为对应字段即可

方案2：指定路径匹配单个字段 #

如果你只需要匹配数组中是否存在任意一个对象的asin等于指定值，可以用以下两种写法：

// 方式A：用JSON_OVERLAPS匹配值是否存在于提取的数组中
$initial_results->whereRaw(
    'JSON_OVERLAPS(JSON_EXTRACT(`info`, "$[*].asin"), ?)',
    [json_encode(['B07V3SSLN11'])]
);

// 方式B：用JSON_SEARCH判断是否存在匹配值
$initial_results->whereRaw(
    'JSON_SEARCH(`info`, "one", ?, null, "$[*].asin") IS NOT NULL',
    ['B07V3SSLN11']
);

方案3：Laravel 9+ 原生JSON查询方法 #

如果你使用的是Laravel 9及以上版本，可以直接用框架封装的whereJsonContains方法，不需要写原生SQL：

$initial_results->whereJsonContains('info', ['sku' => 'DTI-LALF3-EA18', 'asin' => 'B07V3SSLN11']);

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