如何从表a出发关联JSONB中的ID实现PostgreSQL查询？

错误原因分析 #

你尝试的查询存在两个核心问题：

1. 表重复引用与连接顺序错误：FROM子句里同时写了b和JOIN b，导致表b被重复引用；且PostgreSQL中JOIN优先级高于逗号分隔的表列表，执行JOIN b时a还未纳入有效查询上下文，因此出现"invalid reference to FROM-clause entry for table 'a'"错误。
2. 关联逻辑混乱：展开jsonb数组的x(j)基于表b的列，需先展开目标用户的b表数组记录，再与a表关联，而非先写a表再乱关联。

正确的SQL查询（从表a出发） #

从表a出发实现需求，需先定位目标用户的b表记录、展开其apps_last_accessed数组，再与a表关联并按访问时间排序：

SELECT
    a.id,
    a.name,
    j ->> 'published' AS published
FROM
    a
JOIN (
    SELECT
        jsonb_array_elements(apps_last_accessed) AS j
    FROM
        b
    WHERE
        b.id = 'b9a528e2'
        AND apps_last_accessed IS NOT NULL
) AS x ON a.id = (j ->> 'local_id')::uuid
ORDER BY (j ->> 'published')::timestamp DESC;

ActiveRecord适配写法 #

适配Rails ActiveRecord可通过子查询实现：

# 先获取目标用户展开后的应用访问记录
user_app_accesses = B.select("jsonb_array_elements(apps_last_accessed) AS j")
                     .where(id: 'b9a528e2')
                     .where.not(apps_last_accessed: nil)
                     .from("b")
                     .as('x')

# 关联表a并按访问时间倒序排序
result = A.joins("JOIN #{user_app_accesses.to_sql} ON a.id = (x.j ->> 'local_id')::uuid")
          .select("a.id, a.name, x.j ->> 'published' AS published")
          .order("(x.j ->> 'published')::timestamp DESC")

该写法既符合从表a出发的查询逻辑，又能适配ActiveRecord，同时解决了原查询的语法错误，满足按最后访问时间排序的需求。

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