WHERE Query on JSON datatype

[saurabhverma2892]

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?

[janmeier]

As you've noticed, JSON is currently only supported in postgres sequelize/sequelize#4727

[saurabhverma2892]

Possible workaround? or can i do a raw query for this?

[janmeier]

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...)

[saurabhverma2892]

@janmeier Works! Thanks! :)

[Demonium]

Just for information JSON type is supported in MySql 5.7

[saurabhverma2892]

@Demonium yes it is. but not supported by sequelize for mysql

[pratik-freightwalla]

        (where: {
            country: {
                '"state"': {
                    '"city"': {
                        $eq: 'Mumbai'
                    }
                }
            }
        });

This worked for me, 3 layers in the JSON object

[papb]

This probably deserves to be better explained in the docs :)

[mahnunchik]

Any news?

[sharadshetty2]

where: {
  facebook: {
     id: profile.id
  }
}

assuming facebook is an object and profile.id is the value which you want to query, this should work