Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

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

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;

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

>Solution :

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"
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading