Sequelize: How to match atleast 1 value from array stored in db with array in the request

user_11387990 0
Viewed 760

I have a table in psql db like below.

[{
 name="ABC",
 email="xyz",
 animals=["cats","dogs","elephants","giraffes"]
},
{
 name="BCD",
 email="fgh",
 animals=["giraffes"]
}]
....

and an array that I'll get in the request like below

hasAnimals=["lion","cheetah","dog","cat"]

How do I use sequelize where clause to get the records of all the people in the above table who have at least 1 of the animals from the hasAnimals array.

For eg. the stated example should fetch me the user with name ABC as ABC has cats & dogs and so does the array that I got in the request

sequelize.js node.js postgresql json
2 Answers

The solution turned out to be really simple.

const { Op } = require("sequelize");

let arr = []
for(let animal of animals) {
    arr.push({
        [Op.contains]: [animal],
    });
}

abc.findAll({
   where: {
      animals: {
         [Op.or]: arr
      }
   },    
})
user_113879900

sql solution could be something like :

SELECT *
  FROM your_table
 WHERE your_json_column :: jsonb @? '$[*].animals[*] ? (@ <@ HasAnimals)'
user_80600170
Related