Laravel 8 + PostgreSQL 12.8中JSON字段角色数据聚合统计需求

场景说明 #

风险管理工具的表中，每行的roles JSON字段存储了安全事件参与用户及对应角色ID（格式为JSON数组，例如[{"user_id":101,"role_id":1},...]）。原逻辑通过PHP拉取全量数据后处理，效率较低，现需将聚合逻辑迁移至PostgreSQL，技术栈为Laravel 8 + PostgreSQL 12.8，roles字段类型为json（非jsonb）。

解决方案 #

1. PostgreSQL原生SQL实现

通过json_array_elements展开JSON数组，再分组统计用户各角色的出现次数：

SELECT
    (role_data->>'user_id')::INT AS user_id,
    (role_data->>'role_id')::INT AS role_id,
    COUNT(*) AS occurrence_count
FROM
    risk_management_events,
    json_array_elements(roles) AS role_data
-- 可选：过滤非法JSON格式（若存在非数组的roles值）
WHERE json_typeof(roles) = 'array'
GROUP BY
    user_id,
    role_id
ORDER BY
    user_id, occurrence_count DESC;

2. Laravel 8 Query Builder实现

对应Laravel代码，通过原生表达式调用PostgreSQL的JSON函数：

use Illuminate\Support\Facades\DB;

$roleStats = DB::table('risk_management_events')
    ->selectRaw("(role_data->>'user_id')::INT AS user_id")
    ->selectRaw("(role_data->>'role_id')::INT AS role_id")
    ->selectRaw('COUNT(*) AS occurrence_count')
    ->crossJoin(DB::raw("json_array_elements(roles) AS role_data"))
    ->whereRaw("json_typeof(roles) = 'array'") // 可选：过滤非数组数据
    ->groupBy('user_id', 'role_id')
    ->orderBy('user_id')
    ->orderByDesc('occurrence_count')
    ->get();

示例统计结果 #

注意事项 #

• 虽然使用的是json类型而非jsonb，json_array_elements函数仍可正常工作，聚合统计性能满足需求；若后续需频繁基于roles字段做条件过滤，可考虑将字段类型改为jsonb以优化索引性能。
• 若表中存在roles非数组的异常数据，需添加WHERE json_typeof(roles) = 'array'过滤，避免查询报错。

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