如何基于JSON列匹配特定值检索数据?whereJsonContains返回空数组问题
问题场景
delivery_charges表存在名为locations的JSON类型列,列内存储的样例数据结构为:
[{"district_id":"1"},{"district_id":"2"},{"district_id":"3"}]
运行如下查询代码时,$deliveryCharges始终返回空数组:
$deliveryCharges = DB::table('delivery_charges') ->whereJsonContains('locations->district_id', post('district_id')) ->get(); dump(post('district_id')); // 输出 1 dump($deliveryCharges); // 输出 []
已完成前置排查:注释掉whereJsonContains()子句后可以正常返回表内所有数据,传入的post('district_id')值确实为1,问题完全出在JSON匹配的where子句逻辑上。
匹配失败的核心原因
- JSON路径定义不符合实际存储结构:
locations列存储为根层级JSON数组,非单个JSON对象。原写法locations->district_id对应逻辑是读取locations根对象下的district_id属性,无法穿透数组层级读取每个数组元素的district_id值,路径完全失效。 - 严格类型校验不匹配:数据库JSON函数执行匹配时会校验值类型,列内存储的
district_id为字符串格式的"1",查询时传入未做类型转换的整型值1,会因为类型差异匹配失败。
正确实现方案
结合上述原因,修正后可正常查询的写法为:
$deliveryCharges = DB::table('delivery_charges') // 使用*通配符*匹配数组所有元素的district_id字段,同时将传入值转为字符串和存储类型对齐 ->whereJsonContains('locations->[*].district_id', (string) post('district_id')) ->get();
如果遇到MySQL 5.7旧版本兼容问题,也可使用原生JSON条件语句实现,逻辑更可控:
$deliveryCharges = DB::table('delivery_charges') ->whereRaw( "JSON_CONTAINS(JSON_EXTRACT(locations, '$[*].district_id'), ?)", [json_encode((string) post('district_id'))] ) ->get();
注意:JSON匹配过程中不要省略类型转换,哪怕存储的ID值看起来是数字,只要存储格式是字符串,就必须转成同类型再匹配,否则查询会静默返回空结果,不会抛出明确报错。
内容的提问来源于stack exchange,提问作者Abdus Sattar Bhuiyan
相关产品推荐
相关产品推荐

