PostgreSQL如何按panel_class聚合生成嵌套JSON结构？

需求说明 #

你需要将包含user_id、panel_class、widget_reference、widget_value的数据集，按panel_class分组，把同组的widget_reference与widget_value转换为键值对形式的嵌套JSON，最终生成一个以panel_class为顶级键的单一JSON结构，用于返回给浏览器作为用户部件偏好配置。

解决方案（PostgreSQL） #

假设你的数据表名为user_widget_preferences，可以通过两层聚合函数实现需求：

SELECT json_object_agg(panel_class, widgets) AS user_preferences
FROM (
    SELECT 
        user_id,
        panel_class,
        json_object_agg(widget_reference, widget_value) AS widgets
    FROM user_widget_preferences
    WHERE user_id = 'target_user_123' -- 替换为目标用户ID
    GROUP BY user_id, panel_class
) AS grouped_widgets;

代码解释 #

• 内层子查询：先按user_id和panel_class分组，使用json_object_agg(widget_reference, widget_value)将同组内的部件参考与值聚合为一个JSON对象（键为widget_reference，值为widget_value）。
• 外层查询：再用json_object_agg(panel_class, widgets)将每个panel_class作为顶级键，对应的部件配置JSON作为值，最终生成完整的用户偏好结构。

处理重复键 #

如果同组内存在重复的widget_reference，可以通过指定排序规则保留优先级最高的记录：

json_object_agg(widget_reference, widget_value ORDER BY created_at DESC) AS widgets

或者添加DISTINCT确保键唯一：

json_object_agg(DISTINCT widget_reference, widget_value) AS widgets

示例输出 #

最终返回的JSON结构如下，可直接用于前端渲染用户部件偏好：

{
  "dashboard_panel": {
    "weather_widget": "show",
    "calendar_widget": "hide"
  },
  "profile_panel": {
    "avatar_widget": "large",
    "bio_widget": "show"
  }
}

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