如何在Eloquent中优雅使用JSON_TABLE实现JSON数组分组统计？

数据库某列存储着类似[5,3,1]的JSON数组，需要对数组中的单个值执行groupBy操作。你目前已经通过原生查询构造器实现了需求，这里提供几种更贴合Eloquent风格的优雅写法：

原实现写法 #

DB::table(DB::raw('records, JSON_TABLE(records.column, "$[*]" COLUMNS(Value INT PATH "$")) AS data'))
    ->where('deleted_at', '=', null)
    ->where('user_id', '=', $this->user->id)
    ->selectRaw('data.Value')
    ->groupBy('data.Value')
    ->toSql();

生成的SQL语句：

select
    data.Value
from
    records,
    JSON_TABLE(
        records.column,
        "$[*]" COLUMNS(Value INT PATH "$")
    ) AS data
where
    `deleted_at` is null
    and `user_id` = 2
group by
    `data`.`Value`

优雅的Eloquent实现方式 #

方式一：利用Eloquent模型+显式交叉连接 #

假设对应的数据模型为Record，可以直接用模型查询构造器结合crossJoin（替代原隐式逗号连接，语义更清晰）：

Record::query()
    ->whereNull('deleted_at')
    ->where('user_id', $this->user->id)
    ->crossJoin(
        DB::raw('JSON_TABLE(records.column, "$[*]" COLUMNS(Value INT PATH "$")) AS data')
    )
    ->select('data.Value')
    ->groupBy('data.Value')
    ->toSql();

方式二：封装查询范围（Scope）实现复用 #

如果需要多次处理这类JSON数组展开的场景，可以在Record模型中封装一个查询范围，提升代码复用性：

在Record模型中添加：

/**
 * 展开JSON数组列，用于后续统计
 * @param \Illuminate\Database\Eloquent\Builder $query
 * @param string $column JSON数组列名
 * @param string $alias 展开后的表别名
 * @param string $valueColumn 展开后值的列名
 * @return \Illuminate\Database\Eloquent\Builder
 */
public function scopeWithJsonArrayValues($query, $column, $alias = 'data', $valueColumn = 'Value')
{
    return $query->crossJoin(
        DB::raw("JSON_TABLE(records.$column, \"$[*]\" COLUMNS($valueColumn INT PATH \"$\")) AS $alias")
    );
}

使用时只需调用该范围，代码更简洁：

Record::query()
    ->whereNull('deleted_at')
    ->where('user_id', $this->user->id)
    ->withJsonArrayValues('column')
    ->select('data.Value')
    ->groupBy('data.Value')
    ->get();

以上两种方式生成的SQL和原实现完全一致，既保留了Eloquent的可读性与便捷性，又避免了直接拼接原生表字符串的生硬写法。

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