如何在PostgreSQL或Rails Active Record中展开JSONB字段实现关联查询

PostgreSQL原生SQL实现 #

PostgreSQL中可通过jsonb_array_elements函数展开JSONB数组，结合CROSS JOIN LATERAL实现与Snowflake flatten一致的效果：

SELECT school.*, student.value
FROM school
CROSS JOIN LATERAL jsonb_array_elements(school.students) AS student;

若需保留无学生数据（students为NULL或空数组）的学校记录，可替换为左连接：

SELECT school.*, student.value
FROM school
LEFT JOIN LATERAL jsonb_array_elements(school.students) AS student ON true;

Rails Active Record实现 #

方式一：直接使用SQL片段 #

通过joins和select传入原生SQL片段快速实现：

School.joins("CROSS JOIN LATERAL jsonb_array_elements(school.students) AS student")
      .select("school.*, student.value")

方式二：使用Arel（更安全的ORM写法） #

借助Arel构造Lateral连接，避免直接拼接SQL字符串：

# 构造展开数组的SQL节点
student_elements = Arel.sql("jsonb_array_elements(school.students) AS student")
# 执行查询
School.joins(Arel::Nodes::CrossJoinLateral.new(student_elements))
      .select("school.*, student.value")

如需处理空数组/NULL的情况，将CrossJoinLateral替换为OuterJoinLateral即可：

School.joins(Arel::Nodes::OuterJoinLateral.new(student_elements, Arel.sql('true')))
      .select("school.*, student.value")

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