How to perform ilike query in array of object jsonb column using sequelize postgres

Question

so i have this data

table name: items
slug     name        metadata
blue    round       [
                       {
                         "value": "sweet",
                         "type": "block",
                        }
                     ]

and i want to do something like this

where: [{
  [Op.or]: [
     { slug: { [Op.iLike]: `%${value}%` } },
     { name: { [Op.iLike]: `%${value}%` } },
     { 'metadata.value': { [Op.iLike]: `%${value}%`}},
  ],
}]

it executing ("items"."metadata"#>>'{value}') ILIKE '%sweet%')

no error but give me no result

thank you in advance

Answer 1

solved this using literal and like regex in raw SQL

literal(`"items"."metadata" @@ '$.* like_regex "${value}" flag "i"' `),

so my query looks like this

where: [{
  [Op.or]: [
    { slug: { [Op.iLike]: `%${value}%` } },
    { name: { [Op.iLike]: `%${value}%` } },
    literal(`"items"."metadata" @@ '$.* like_regex "${value}" flag "i"' `),
  ],
}]