possible to reference a jsonb_array_length in a where clause

Question

I am just starting to use some of the jsonb functionality in Postgres 9.4 (would love to move to 9.6) . I have the following query:

SELECT
 queryable_type, 
 jsonb_array_length(as_data->'users_who_like') as js_users_count 
FROM 
 queryables 
WHERE 
 queryable_type = 'Item' and jt_users_count > 1;

but I am unable to access jt_users_count in the where phrase but can like this:

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
 jsonb_array_length(as_data->'users_who_like') desc;

To be honest, this just seems a bit unusual and possibly wasteful. Is there a way for Postgres to only compute this once and use a reference to jt_users_count?

Answer 1

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:

• SQL Query Order of Operations
• You Can Reference Your Derived SELECT Columns In Your ORDER BY Clause In SQL
• SQL Programming: Understanding the Logical Order of Operations in SELECT statements
• Order of execution of a Query