TypeORM如何查询jsonb数组中包含指定exercise id的Training实体

错误原因 #

你原来的查询写法存在两个核心问题：

1. workoutExercises->>id 是尝试读取jsonb字段的顶层id属性，但workoutExercises本身是数组结构，不存在顶层id字段，id是数组内每个子元素的属性
2. IN运算符无法直接用于匹配json数组内的嵌套属性值

正确写法 #

推荐使用PostgreSQL原生的jsonb包含运算符@>实现，写法最简单性能也最优（jsonb字段建了GIN索引的话可以命中索引）：

this.createQueryBuilder('training')
  .where('training.workoutExercises @> :exerciseFilter', {
    // 注意要把匹配条件序列化为JSON字符串，结构是包含目标id元素的数组
    exerciseFilter: JSON.stringify([{ id: exerciseId }])
  })
  .getMany();

如果有更复杂的过滤需求（比如同时要匹配组数、重量等其他属性），可以用展开json数组的方式实现：

this.createQueryBuilder('training')
  // 展开workoutExercises数组为单行的exercise元素
  .innerJoin("jsonb_array_elements(training.workoutExercises)", "exercise")
  // 匹配元素id，::int是把json取出的字符串转为数值和参数类型对齐
  .where("(exercise->>'id')::int = :exerciseId", { exerciseId })
  // 可选：如果单个Training的数组里有多个匹配项，去重避免返回重复的Training实体
  .distinct(true)
  .getMany();

注意事项 #

如果你的workoutExercises字段允许为null，可以在where条件里追加AND training.workoutExercises IS NOT NULL避免空值引发的查询异常。

内容的提问来源于stack exchange，提问作者L. Baptiste