PostgreSQL 10/11查询优化：如何避免SELECT与关联条件表达式重复？

当然可以！重复书写同一个JSON路径表达式不仅冗余，还会让后续的查询维护变得麻烦，针对你的场景，这里有两种实用的优化方法：

方案1：扩展CTE提前计算字段 #

你可以在初始的CTE t 里提前把需要重复使用的JSON字段提取出来并命名，后续的JOIN和SELECT语句直接引用这个别名即可，避免重复调用JSON路径：

WITH t AS (
    SELECT 
        place_id,
        formatted,
        full_json,
        full_json->5->>'short_name' AS state_short_name  -- 提前计算并赋值别名
    FROM addresses_autocomplete 
    WHERE json_array_length(full_json) > 7
), r AS (
    SELECT short_name FROM regions WHERE regions.country_id = 1
)
SELECT DISTINCT ON(place_id)
    t.full_json->5->'short_name' AS state,
    t.full_json->7->'long_name' as postal_code,
    full_json->3->'short_name' AS city
FROM t 
INNER JOIN r ON r.short_name = t.state_short_name  -- 直接使用别名
WHERE t.formatted = $1;

如果想要进一步简化主查询，还可以把其他需要用到的JSON字段也提前在CTE中计算：

WITH t AS (
    SELECT 
        place_id,
        formatted,
        full_json->5->'short_name' AS state,
        full_json->7->'long_name' as postal_code,
        full_json->3->'short_name' AS city,
        full_json->5->>'short_name' AS state_short_name
    FROM addresses_autocomplete 
    WHERE json_array_length(full_json) > 7
), r AS (
    SELECT short_name FROM regions WHERE regions.country_id = 1
)
SELECT DISTINCT ON(place_id)
    state,
    postal_code,
    city
FROM t 
INNER JOIN r ON r.short_name = t.state_short_name
WHERE t.formatted = $1;

方案2：使用LATERAL子查询集中提取JSON属性 #

PostgreSQL 10及以上版本支持LATERAL子查询，它可以帮你把所有JSON字段的提取逻辑集中在一个地方，让主查询的结构更清晰：

WITH r AS (
    SELECT short_name FROM regions WHERE regions.country_id = 1
)
SELECT DISTINCT ON(a.place_id)
    j.state,
    j.postal_code,
    j.city
FROM addresses_autocomplete a
INNER JOIN LATERAL (
    SELECT
        a.full_json->5->'short_name' AS state,
        a.full_json->7->'long_name' as postal_code,
        a.full_json->3->'short_name' AS city,
        a.full_json->5->>'short_name' AS state_short_name
) j ON true
INNER JOIN r ON r.short_name = j.state_short_name
WHERE json_array_length(a.full_json) > 7
  AND a.formatted = $1;

这种方式把所有JSON路径的调用都放在LATERAL子查询里，主查询只需要引用提取好的别名，后续修改JSON路径时只需要在一个地方调整，维护成本更低。

内容的提问来源于stack exchange，提问作者GrandFleet