sql - PostgresSQL Cannot order by json_build_object result (got from subquery)

Question

I have a SQL query. And I'd like to order by json field:

SELECT "ReviewPacksModel"."id",
        (SELECT json_build_object(
                  'totalIssues', COUNT(*),
                  'openIssues', COUNT(*) filter (where "issues".status = 'Open'),
                  'fixedIssues', COUNT(*) filter (where "issues".status = 'Fixed')
                )
         FROM "development"."issues" "issues"
           JOIN "development"."reviewTasks" as "rt" ON "issues"."reviewTaskId" = "rt".id
         WHERE "issues"."isDeleted" = false
           AND "rt"."reviewPackId" = "ReviewPacksModel"."id"
        ) as "issueStatistic"
FROM "development"."reviewPacks" AS "ReviewPacksModel"
WHERE "ReviewPacksModel"."projectId" = '2'
  AND "ReviewPacksModel"."mode" IN ('Default', 'Live')
  AND "ReviewPacksModel"."status" IN ('Draft', 'Active')
ORDER BY "issueStatistic"->'totalIssues'
LIMIT 50;

And I get an error:

ERROR: column "issueStatistic" does not exist

If I try to order by issueStatistic without ->'totalIssues', I will get another error:

ERROR: could not identify an equality operator for type json

It seems like I cannot extract field from the JSON.

I also tested it with this query:

SELECT "ReviewPacksModel".*,
        (SELECT Count(*)
           FROM "development"."issues" "issues"
             JOIN "development"."reviewTasks" as "rt" ON "issues"."reviewTaskId" = "rt".id
           WHERE "issues"."isDeleted" = false
             AND "rt"."reviewPackId" = "ReviewPacksModel"."id"
        ) AS "issueStatistic"
FROM "development"."reviewPacks" AS "ReviewPacksModel"
WHERE "ReviewPacksModel"."projectId" = '2'
  AND "ReviewPacksModel"."mode" IN ('Default', 'Live')
  AND "ReviewPacksModel"."status" IN ('Draft', 'Active')
ORDER BY "issueStatistic"
LIMIT 50;

And it works without any problems. But I cannot use it cause it's not possible to return multiple columns from a subquery. I also tried to use alternatives like array_agg, json_agg, etc. but it doesn't help.

I know that it's possible to make multiple queries, but they aren't super fast and for me it's better to use json_build_object.

question from:https://stackoverflow.com/questions/65951945/postgressql-cannot-order-by-json-build-object-result-got-from-subquery

Answer 1

You can use aliases in ORDER BY, but you cannot use expressions involving aliases.

You'll have to use a subquery.

Also, you cannot order on a json. You'll have to convert it to a sortable data type. In the following I assume it is a number; you'll have to adapt the query if my assumption is wrong.

SELECT id, "issueStatistic"
FROM (SELECT "ReviewPacksModel"."id",
             (SELECT json_build_object(
                       'totalIssues', COUNT(*),
                       'openIssues', COUNT(*) filter (where "issues".status = 'Open'),
                       'fixedIssues', COUNT(*) filter (where "issues".status = 'Fixed')
                     )
              FROM "development"."issues" "issues"
                JOIN "development"."reviewTasks" as "rt" ON "issues"."reviewTaskId" = "rt".id
              WHERE "issues"."isDeleted" = false
                AND "rt"."reviewPackId" = "ReviewPacksModel"."id"
             ) as "issueStatistic"
     FROM "development"."reviewPacks" AS "ReviewPacksModel"
     WHERE "ReviewPacksModel"."projectId" = '2'
       AND "ReviewPacksModel"."mode" IN ('Default', 'Live')
       AND "ReviewPacksModel"."status" IN ('Draft', 'Active')
    ) AS subq
ORDER BY CAST ("issueStatistic"->>'totalIssues' AS bigint)
LIMIT 50;