PostgreSQL中array_agg加ORDER BY出现AS附近语法错误如何解决

错误原因 #

• 聚合函数的ORDER BY位置错误：PostgreSQL 中array_agg这类聚合函数的排序规则需要写在函数括号内部，而非AS 别名之前，你当前的写法不符合PG语法规范。
• 列引用错误：order by 'order' ASC中使用单引号包裹的'order'是字符串常量，不是表中的order字段，无法实现按字段排序的效果，因为order是SQL保留关键字，需要用双引号"order"引用该列。
• 多余语法符号：两个array_agg语句之间、第二个array_agg语句末尾都多写了多余的逗号，同时原代码中第一个CTE内存在表别名引用笔误，也会触发语法报错。

修正后的SQL代码 #

WITH qs AS (
    SELECT
        "question".*,
        array_agg(jsonb_build_object(
            'id',  "responses"."id",
            'questionId', "responses"."questionId",
            'title', "responses"."title",
            'createdAt', "responses"."createdAt",
            'updatedAt', "responses"."updatedAt"
        )) AS "responses"
    FROM question AS "question"
    LEFT OUTER JOIN "question_response" AS "responses" ON "question"."id" = "responses"."questionId" AND "responses"."supervisionId" = 59
    WHERE "question".id = 135
    GROUP BY "question".id, "question".title, "question"."createdAt", "question"."updatedAt"
), qs_op AS (
    SELECT
        qs.*,
        array_agg(jsonb_build_object(
            'id',  "options"."id",
            'text', "options"."text",
            'score', "options"."score",
            'order', "options"."order"
        ) ORDER BY "options"."order" ASC) AS "options",
        array_agg(jsonb_build_object(
            'id',  "fields"."id",
            'name', "fields"."name",
            'label', "fields"."label",
            'order', "fields"."order",
            'isNumeric', "fields"."isNumeric"
        ) ORDER BY "fields"."order" ASC) AS "fields"
    FROM qs
    LEFT OUTER JOIN "question_option" AS "options" ON qs.id = "options"."questionId"
    LEFT OUTER JOIN "question_field" AS "fields" ON qs.id = "fields"."questionId"
    GROUP BY qs.id, qs.title, qs."createdAt", qs."updatedAt", qs."responses"
), qs_op_2 AS (
    SELECT
        qs_op.*,
        array_agg(jsonb_build_object(
            'id',  "ft"."id",
            'name', "ft"."name"
        )) AS "associatedFacilityTypes"
    FROM qs_op
    LEFT OUTER JOIN ( "question_facility_type" AS "iqf" INNER JOIN "fac_type" AS "ft" ON "ft"."id" = "iqf"."facilityTypeId") ON qs_op.id = "iqf"."questionId"
    GROUP BY qs_op.id, qs_op.title, qs_op."createdAt", qs_op."updatedAt", qs_op."responses", qs_op."options", qs_op."fields"
)
SELECT * FROM qs_op_2
ORDER BY qs_op_2.id;

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