如何用Ecto实现PostgreSQL中JSONB字段的ref值查询？

针对你需求的场景，这里给出完整的Ecto查询实现，完美对应你提供的PostgreSQL SQL语句：

from c in Stuff,
  cross_join: f1 in fragment("jsonb_each(?)", c.stuff),
  where: fragment("?->>'ref' = ?", f1.value, "ref_1"),
  select: c

关键部分解释： #

• cross_join：对应SQL里的CROSS JOIN，jsonb_each(c.stuff)会把stuff字段里的每个JSON键值对拆分成单独的行，交叉连接后就能逐行检查每个类型的ref值。
• 别名f1：给jsonb_each返回的结果集起别名，这样我们可以引用它默认的key（JSON的键，比如type1、type2）和value（对应的JSON对象）字段。
• where条件：用fragment直接调用PostgreSQL的JSONB操作符，?->>'ref'会从f1.value这个JSON对象里提取ref字段的文本值，然后和目标值"ref_1"做匹配。

如果需要同时返回拆分后的类型键（比如type1）和对应的JSON对象，可以调整select部分：

from c in Stuff,
  cross_join: f1 in fragment("jsonb_each(?)", c.stuff),
  where: fragment("?->>'ref' = ?", f1.value, "ref_1"),
  select: %{
    record: c,
    type_key: f1.key,
    type_data: f1.value
  }

另外，如果你的stuff字段是json类型而不是jsonb，只需要把jsonb_each改成json_each即可。

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