如何用Laravel Query Builder查询products表的JSON类型字段数据？

假设products表包含id、prodcatkey常规字段，以及JSON类型字段pricematrix，字段数据示例如下：

[{"packing":"1","size":"500ml","vencode":"1EIFEL","price":"527.50"},{"packing":"3","size":"500ml","vencode":"1EIFEL","price":"1582.50"},{"packing":"6","size":"500ml","vencode":"1EIFEL","price":"3165.00"},{"packing":"12","size":"500ml","vencode":"1EIFEL","price":"6330.00"}]

以下是用Laravel Query Builder实现需求的具体写法：

1. 获取JSON数组的第一个对象 #

直接利用数据库的JSON索引提取语法（以MySQL为例，数组索引从0开始）：

$product = DB::table('products')
    ->select(
        'id',
        'prodcatkey',
        DB::raw('pricematrix->\'$[0]\' as first_price_item')
    )
    ->where('id', 1) // 根据实际需求添加筛选条件
    ->first();

返回的first_price_item就是数组里的第一个完整JSON对象。

2. 获取第一个对象的特定字段（比如price） #

如果只需要第一个对象里的某个字段，直接指定字段路径即可：

$product = DB::table('products')
    ->select(
        'id',
        'prodcatkey',
        DB::raw('pricematrix->\'$[0].price\' as first_item_price')
    )
    ->where('id', 1)
    ->first();

此时first_item_price会直接返回"527.50"。

3. 获取数组中符合特定条件的对象 #

比如要提取packing为"3"的对象，可以结合whereJsonContains筛选，再提取对应内容：

// 先筛选出包含目标对象的记录，再提取所有符合条件的项
$product = DB::table('products')
    ->select(
        'id',
        'prodcatkey',
        DB::raw('json_extract(pricematrix, \'$[*]\') as matching_items')
    )
    ->whereJsonContains('pricematrix', ['packing' => '3'])
    ->first();

// 或者直接定位到目标对象的路径并提取
$product = DB::table('products')
    ->select(
        'id',
        'prodcatkey',
        DB::raw('json_extract(pricematrix, json_search(pricematrix, \'one\', \'3\', null, \'$[*].packing\')) as target_item')
    )
    ->where('id', 1)
    ->first();

注意事项 #

不同数据库的JSON函数语法略有差异：

• PostgreSQL 中可以用pricematrix->0获取第一个元素，或者jsonb_array_elements展开数组
• SQL Server 则使用JSON_VALUE或JSON_QUERY函数

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