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.

Laurenz Albe · Accepted Answer · 2021-01-29 09:53:30Z

2

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;

edited Jan 29, 2021 at 9:53

answered Jan 29, 2021 at 9:42

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Laurenz Albe Over a year ago

@S-Man I didn't think of that. Fixed.

Kirill Varikov Over a year ago

Thank you a lot for your answer. Your solution works! The root cause is that I cannot use expression result in order by? Is it correct? Yeah, now I have to find out a way how to compose this query by my ORM, but this is another problem :)

S-Man · Accepted Answer · 2021-01-29 14:56:58Z

2

demos:db<>fiddle

You cannot order by type json because, simply spoken, there is no definition on how to handle different types included in the JSON object. But this gives you a type json:

"issueStatistic"->'totalIssues'

However, type jsonb can be ordered. So, instead of creating a type json object, you should use jsonb_build_object() to create a type jsonb object.

Alternatively you could cast your expression into type int (mind the ->> operator instead of your -> which casts the output into type text which can be directly cast into type int):

("issueStatistic"->>'totalIssues')::int

Edit:

As Laurenz mentioned correctly, to use aliases you need a separate subquery:

SELECT 
    *
FROM (
    -- <your query minus ORDER clause>
) s
ORDER BY "issueStatistic"->'totalIssues'

edited Jan 29, 2021 at 14:56

answered Jan 29, 2021 at 9:43

S-Man

24k9 gold badges51 silver badges78 bronze badges

2 Comments

Kirill Varikov Over a year ago

Thank you for answer. Unfortunately it doesn't help. I still get an error: ERROR: column "issueStatistic" does not exist. I changed to jsonb_build_object() and used this cast ("issueStatistic"->>'totalIssues')::int. Any ideas?

S-Man Over a year ago

@KirillVarikov Did this help? If any answer helped, don't forget to UPVOTE all of them (that honors the time and effort, the repliers invested into your problem). If one of them solves your problem, please don't forget to ACCEPT additionally (this closes the question).

Collectives™ on Stack Overflow

PostgresSQL Cannot order by json_build_object result (got from subquery)

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related