PostgreSQL使用json_agg报错：字段需在GROUP BY或聚合函数中

错误原因 #

你遇到的错误是因为使用json_agg聚合函数后，整个查询属于无GROUP BY的聚合查询（会将所有匹配行合并为一行），而ORDER BY子句中的data_de_inicio_da_coleta既不在GROUP BY列表中，也没有被聚合函数包裹，违反了PostgreSQL的聚合查询规则。

解决方案 #

正确的查询写法（先分页排序再聚合） #

要实现筛选、分页、排序后将结果聚合成JSON数组（直接对应Python的字典列表），可以用子查询先处理行的筛选、排序和分页，再对子查询结果进行聚合：

sql = """SELECT json_agg(sub)
         FROM (
             SELECT *
             FROM survey_data.survey_data 
             WHERE codigo_do_projeto LIKE '%%%s%%'
             ORDER BY data_de_inicio_da_coleta desc 
             LIMIT %s
             OFFSET %s*%s
         ) AS sub""" % (survey_name, items_per_page, items_per_page, page_number)

更安全的参数化写法（避免SQL注入） #

直接字符串拼接SQL存在SQL注入风险，建议使用数据库驱动的参数化查询（以psycopg2为例）：

sql = """SELECT json_agg(sub)
         FROM (
             SELECT *
             FROM survey_data.survey_data 
             WHERE codigo_do_projeto LIKE %s
             ORDER BY data_de_inicio_da_coleta desc 
             LIMIT %s
             OFFSET %s
         ) AS sub"""
# 通配符%放在参数中，而非SQL语句内
params = (f"%{survey_name}%", items_per_page, items_per_page * page_number)
# 执行查询
cursor.execute(sql, params)

结果处理 #

执行上述查询后，通过fetchone()[0]获取结果，psycopg2会自动将PostgreSQL返回的JSON数组解析为Python的字典列表，无需额外转换即可直接使用。

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