如何直接查询orders表JSON列中指定时段的热销商品数据？

开发者阿泽

2026-8-19

指定时间段内订购量最高商品的高效查询方案

问题场景

orders表存在details JSON列，商品订购信息存储格式如下：

{
    "products": [
        {
            "id": 1,
            "quantity": 2
        },
        {
            "id": 6,
            "quantity": 1
        }
    ]
}

当前实现会拉取所有符合时间条件的订单数据到PHP中处理，效率较低：

Order::whereBetween('created_at', $period)
    ->get('details->products as details')
    ->flatMap(fn ($order) => $order->details)
    ->groupBy('id')
    ->map(fn ($products) => $products->sum('quantity'));

返回结果为商品ID与总订购量的键值对：

[
    1 => 2,
    6 => 1,
];

可以直接通过数据库查询获取商品ID与订购量数据，且效率会大幅提升。

数据库层面的高效实现

将JSON数组展开、分组求和的逻辑下推到数据库，避免拉取大量冗余数据到PHP处理，以下是主流数据库的实现：

MySQL/MariaDB

利用JSON_TABLE函数将JSON数组转为行结构，再分组统计：

$stats = Order::query()
    ->selectRaw('product.id as product_id, SUM(product.quantity) as total_quantity')
    ->whereBetween('created_at', $period)
    ->fromRaw('orders, JSON_TABLE(details->"$.products", "$[*]" COLUMNS(id INT PATH "$.id", quantity INT PATH "$.quantity")) as product')
    ->groupBy('product.id')
    ->orderByDesc('total_quantity')
    ->pluck('total_quantity', 'product_id');

PostgreSQL

使用jsonb_array_elements展开JSON数组，再做类型转换与统计：

$stats = Order::query()
    ->selectRaw('(product->>\'id\')::int as product_id, SUM((product->>\'quantity\')::int) as total_quantity')
    ->whereBetween('created_at', $period)
    ->fromRaw('orders, jsonb_array_elements(details->\'products\') as product')
    ->groupBy('product_id')
    ->orderByDesc('total_quantity')
    ->pluck('total_quantity', 'product_id');