在Rails 5.2中用Arel查询jsonb数组中Project类型订阅的ID

我来帮你搞定这个问题！你之前用@>运算符没成功，是因为它的作用是检查整个jsonb数组是否包含某个元素，而不是提取符合条件的元素里的具体字段。要拿到所有type为Project的订阅id，我们需要先把jsonb数组拆分成单个元素，过滤后再提取目标字段，用Arel可以这样实现：

步骤1：构建Arel基础对象 #

首先获取User表的Arel对象和subscriptions字段：

users_table = User.arel_table
subscriptions_col = users_table[:subscriptions]

步骤2：展开jsonb数组为行 #

使用PostgreSQL的jsonb_array_elements函数把数组拆分成独立的行记录，用Arel的NamedFunction来调用这个函数：

array_elements = Arel::Nodes::NamedFunction.new(
  'jsonb_array_elements',
  [subscriptions_col]
).as('sub')

步骤3：过滤出type为Project的元素 #

用->>运算符提取元素的type字段，然后匹配Project：

type_match = Arel::Nodes::InfixOperation.new(
  '->>',
  Arel.sql('sub'),
  Arel.sql("'type'")
).eq('Project')

步骤4：提取目标id字段 #

同样用->>运算符提取符合条件元素的id：

project_id = Arel::Nodes::InfixOperation.new(
  '->>',
  Arel.sql('sub'),
  Arel.sql("'id'")
)

步骤5：执行查询并获取结果 #

把这些部分组合起来，执行查询就能拿到所有符合条件的id了：

project_subscription_ids = User.select(project_id.as('project_id'))
                               .from(users_table, array_elements)
                               .where(type_match)
                               .pluck('project_id')

额外需求：按用户聚合订阅ID #

如果需要获取每个用户对应的所有Project订阅ID（而不是所有id的平铺列表），可以用jsonb_agg来聚合结果：

aggregated_ids = Arel::Nodes::NamedFunction.new(
  'jsonb_agg',
  [project_id]
).as('project_subscription_ids')

user_project_subscriptions = User.select(users_table[:id], aggregated_ids)
                                 .from(users_table, array_elements)
                                 .where(type_match)
                                 .group(users_table[:id])

这样返回的每个结果会包含用户id和对应的所有Project订阅ID数组。

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