如何在Nest.js+TypeORM中查询JSONB列嵌套数组对象的指定值？

我需要在Nest.js+TypeORM的环境下，基于PostgreSQL的JSON字段实现技能项搜索：

• 搜索值：'cooking'
• 数据库表中JSON字段data的结构：

data: {
   skills: {
      items: [ { name: 'cooking' }, ... ]
   }
}

期望实现：找出所有data->skills->items数组中存在name字段包含（或精确匹配）'cooking'的记录。

当前代码仅支持按userId查询：

const allItems = this.dataRepository.find({ where: [{ user: { id: userId } }] })

我已查阅PostgreSQL的JSON函数，能写出原生SQL，但不知道如何转成TypeORM写法。另外试过以下原生SQL，能实现精确匹配，但维护性差，也无法做模糊匹配：

SELECT *
FROM table1 t
WHERE t.data->'skills' @> '{"items":[{"name":"cooking"}]}';

方案1：精确匹配（对应原生@>操作符） #

用FindOptions写法 #

借助TypeORM的Raw函数直接调用PostgreSQL的JSON操作符：

const searchValue = 'cooking';
const items = await this.dataRepository.find({
  where: [
    { user: { id: userId } },
    {
      data: Raw(
        (alias) => `${alias}->'skills' @> '{"items":[{"name":"${searchValue}"}]}'`
      )
    }
  ]
});

用QueryBuilder写法（更安全易维护） #

通过参数绑定避免SQL注入，可读性更强：

const searchValue = 'cooking';
const items = await this.dataRepository
  .createQueryBuilder('t')
  .where('t.user.id = :userId', { userId })
  .andWhere(`t.data->'skills' @> :jsonPattern`, {
    jsonPattern: JSON.stringify({ items: [{ name: searchValue }] })
  })
  .getMany();

方案2：模糊匹配（匹配包含'cooking'的任意值） #

如果需要匹配name中包含cooking的项（比如cooking pasta、chinese cooking），可以用以下两种方式：

方式1：使用jsonb_path_query #

const searchValue = 'cooking';
const items = await this.dataRepository
  .createQueryBuilder('t')
  .where('t.user.id = :userId', { userId })
  .andWhere(`EXISTS (
    SELECT 1 FROM jsonb_path_query(t.data, '$.skills.items[*].name') AS item_name
    WHERE item_name::text LIKE :searchPattern
  )`, {
    searchPattern: `%${searchValue}%`
  })
  .getMany();

方式2：使用jsonb_array_elements #

const searchValue = 'cooking';
const items = await this.dataRepository
  .createQueryBuilder('t')
  .where('t.user.id = :userId', { userId })
  .andWhere(`EXISTS (
    SELECT 1 FROM jsonb_array_elements(t.data->'skills'->'items') AS item
    WHERE item->>'name' LIKE :searchPattern
  )`, {
    searchPattern: `%${searchValue}%`
  })
  .getMany();

注意事项 #

• 始终用参数绑定（:param）代替直接拼接字符串，避免SQL注入风险。
• 如果你的data字段是json类型而非jsonb，将上述代码中的jsonb_*函数替换为json_*即可。
• QueryBuilder写法比FindOptions更适合复杂JSON查询，扩展性和可读性更好。

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