sequelize
WHERE Query on JSON datatype
Hey,
I have a column which is JSON datatype and contains Id, Email, etc inside it in the form of json I am trying to get a user from the database where facebook.id = "some value"
User.find({where:{'facebook.id': profile.id}})
but this gives a syntax error: Executing (default): SELECT _id, name, email, role, password, provider, salt, facebook, twitter, google, github, createdAt, updatedAt FROM users AS User WHERE (User.facebook#>>'{id}') = '1239871587612' LIMIT 1; SequelizeDatabaseError: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
I guess the error is at the syntax used at { 'facebook.id': profile.id}.
How can we query on the parameters inside JSON?
a raw query for the same which works looks like: SELECT name from users As User WHERE facebook->"$.id" = "1239871587612”;
possible solutions?
As you've noticed, JSON is currently only supported in postgres https://github.com/sequelize/sequelize/issues/4727
Completely raw query yes, or you could use literal:
User.findAll({
where: {
$and: [
sequelize.where(
sequelize.literal('facebook->"$.id"'),
"1239871587612"
)
]
}
});
(this example shows how the condition might be combined with other, regular where clauses - if you dont need that you can simply do where: sequelize.where...)
(where: {
country: {
'"state"': {
'"city"': {
$eq: 'Mumbai'
}
}
}
});
This worked for me, 3 layers in the JSON object
where: {
facebook: {
id: profile.id
}
}
assuming facebook is an object and profile.id is the value which you want to query, this should work
