This is the standard behaviour of SQL. SQL statements are (logically) processed in a certain specified order.
Following that order, the WHERE
clause is executed before the SELECT
one. That's why the WHERE
clause cannot access the value of js_users_count
: It has not yet been computed.
That's why your second query works, but your first one doesn't.
On the other hand, it is possible to have js_users_count
on the ORDER BY
, because that clause is processed after the SELECT
:
SELECT
queryable_type,
jsonb_array_length(as_data->'users_who_like') as js_users_count
FROM
queryables
WHERE
queryable_type = 'Item' and jsonb_array_length(as_data->'users_who_like') > 1
ORDER BY
js_users_count desc;
Don't be afraid about a certain computation being done twice. It can be either optimized (as some kind of common subexpression elimination optimization) or its impact is completely neglectable in the overall performance of a query.
If you want to really make sure that an expression is not evaluated twice, you can use one (often annoying) feature of PostgreSQL CTE: the fact that they're optimization fences (i.e.: they won't be inlined, or converted to subqueries in the final statement):
WITH q1 AS
(
SELECT
queryable_type,
jsonb_array_length(as_data->'users_who_like') as js_users_count
FROM
queryables
WHERE
queryable_type = 'Item'
)
SELECT
*
FROM
q1
WHERE
js_users_count > 1
ORDER BY
js_users_count desc;
This will create a (virtual temporary) table q1
with the already computed values of jsonb_array_length
. I would only do this kind of thing if the function used takes a very long time to execute (has a very high cost), and there's evidence that it's actually called twice (you can check with some RAISE DEBUG
statements in the function itself, if it's been written by you).
References: