PostgreSQL 15创建嵌套对象结构遇聚合函数不可嵌套错误求助

遇到的错误 #

ERROR: aggregate function calls cannot be nested

期望生成的JSON结构 #

"characteristics": {
    "Size": {
      "id": 14,
      "value": "4.0000"
    },
    "Width": {
      "id": 15,
      "value": "3.5000"
    },
    "Comfort": {
      "id": 16,
      "value": "4.0000"
    }
}

表结构定义 #

CREATE TABLE IF NOT EXISTS characteristics
(
    id integer PRIMARY KEY UNIQUE,
    product_id integer,
    name text
);
CREATE TABLE IF NOT EXISTS ratingschar
(
    id serial PRIMARY KEY,
    characteristics_id integer,
    review_id integer,
    value integer
);

当前错误查询语句 #

(select jsonb_object_agg(name, jsonb_object_agg(some_value,therating)) as chars
from (select name, AVG(value) as some_value, ratingschar.id as therating from
        ratingschar
inner join characteristics
    on ratingschar.id = characteristics.id
where product_id = 465464
GROUP BY name, ratingschar.id
      ) as b
     ) 

问题分析与解决方案 #

1. 关联条件错误：原查询中ratingschar.id = characteristics.id是错误的，应该用ratingschar.characteristics_id = characteristics.id，这才是两张表的正确外键关联关系。
2. 聚合函数不能嵌套：PostgreSQL不允许嵌套调用jsonb_object_agg这类聚合函数，需要先为每个特征组装好单个JSON对象，再进行外层聚合。

正确查询语句（生成完整结构）

SELECT jsonb_build_object(
    'characteristics',
    jsonb_object_agg(c.name, jsonb_build_object('id', c.id, 'value', ROUND(AVG(r.value)::numeric, 4)::text))
) AS result
FROM characteristics c
JOIN ratingschar r ON c.id = r.characteristics_id
WHERE c.product_id = 465464
GROUP BY c.product_id;

简化版（仅返回characteristics对应的JSON）

SELECT jsonb_object_agg(c.name, jsonb_build_object('id', c.id, 'value', ROUND(AVG(r.value)::numeric, 4)::text)) AS characteristics
FROM characteristics c
JOIN ratingschar r ON c.id = r.characteristics_id
WHERE c.product_id = 465464
GROUP BY c.product_id;

逻辑说明 #

• 通过正确的JOIN关联特征表和评分表，确保数据匹配准确。
• 用AVG(r.value)计算每个特征的平均评分，ROUND(...,4)保留四位小数并转为字符串，匹配期望的格式。
• 用jsonb_build_object为每个特征生成包含id和value的JSON对象。
• 外层用jsonb_object_agg以特征名为键、对应JSON对象为值，聚合为最终的嵌套结构。

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