My http request will return below data:
It returns below data:
Users.js
{
{
...
friends:[]
},
{
...
friends:[{id:xxx,...},...]
},
{
...
friends:[]
},
}
If I want to use query to get all data whose friends array is [],
should I do below query.
select * from users where (what should I write here)
If friends is a direct column in your database is JSON array. You can use JSON_LENGTH to find out the length of array.
SELECT JSON_LENGTH('[1, 2, {"a": 3}]'); // Output: 3
SELECT JSON_LENGTH('[]'); // Output: 0
You can use same concept to get data from your database.
select *
FROM users
WHERE JSON_LENGTH(friends) = 0;
If you've nested JSON and one of key is friends in that json for given column(data) then your query would be like using JSON_CONTAINS
SELECT *
FROM users
WHERE JSON_CONTAINS(data, JSON_ARRAY(), '$.friends') -- To check do we have `friends` as key in that json
and JSON_LENGTH(data, '$.friends') = 0; -- To check whether it is empty array.
Now you can convert it to sequelize query. One of the way you can use is
Model.findAll({
where: {
[Op.and]: [
Sequelize.literal('RAW SQL STATEMENT WHICH WONT BE ESCAPED!!!')
]
}
})
Make sure to update Model with your user model and query.
