可以在where子句中引用jsonb_array_length

Question

我刚刚开始使用Postgres 9.4中的一些jsonb功能(很想移到9.6)。我有以下查询：

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;

但我无法访问where短语中的jt_users_count，但我可以这样做：

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;

老实说，这看起来有点不寻常，而且可能是浪费。是否有一种方法使Postgres只计算一次并使用对jt_users_count的引用？

Answer 1

这是SQL的标准行为。SQL语句按特定的指定顺序(逻辑上)处理。

按照该顺序，WHERE子句在SELECT子句之前执行。这就是为什么WHERE子句不能访问js_users_count的值:它还没有被计算出来。

这就是为什么您的第二个查询工作，但您的第一个查询不工作。

另一方面，可以在js_users_count上使用ORDER BY，因为该子句是在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;

不要害怕某个计算被做了两次。可以对其进行优化(作为某种常见的子表达式消除优化)，也可以在查询的总体性能中完全忽略它的影响。

如果您真的想确保一个表达式不会被计算两次，那么您可以使用PostgreSQL CTE的一个特性(通常很烦人)：它们是优化围栏 (即:它们不会被内联，或者在最后语句中转换为子查询)：

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;

这将创建一个(虚拟临时)表q1，其中已经计算出jsonb_array_length的值。如果使用的函数执行时间很长(成本很高)，并且有证据表明它实际上被调用了两次(如果它是由您编写的，您可以使用函数本身中的一些RAISE DEBUG语句进行检查)，我才会这样做。

参考文献：

• SQL查询操作顺序
• 可以在SQL中按顺序引用派生的选择列.
• SQL编程:理解选择语句中操作的逻辑顺序
• 查询的执行顺序