How can I correct this syntax error in this sql query with array

I am trying to include order by in line 35 and 43 in the query below. I want to order the option and field models by 'order' in ASC. But I am getting a syntax error in

syntax error at or near "AS"
LINE 42:      )) AS "fields"

I am using postgresql. The full code is below:

WITH qs AS (
                SELECT
                    "issQuestion".*,
                    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"
                    )) AS "options"
                    order by 'order' ASC,
                    array_agg(jsonb_build_object(
                        'id',  "fields"."id",
                        'name', "fields"."name",
                        'label', "fields"."label",
                        'order', "fields"."order",
                        'isNumeric', "fields"."isNumeric"
                    )) AS "fields"
                    order by 'order' ASC,
                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;

CodePudding user response：

It looks like the ORDER BY corresponds to the data used inside ARRAY_AGG() function. In which case it must be placed inside the function (complete syntax is described here):

array_agg(jsonb_build_object(
  'id',  "options"."id",
  'text', "options"."text",
  'score', "options"."score",
  'order', "options"."order"
) ORDER BY "options"."order" ASC) AS "options"