Rails JSON query combined with other data types

Question

I have a Contact model with a string and json attribute the string attribute stores the phone_number and the json attribute stores some other data.

I want to run a query like this

Contact.exists?(phone_number: '+255788778899', data: {name: 'Gamma',   
region: 'The Great one'})

I have searched all over google and SO, but most of the articles simple give a way of find the record with a json string only and which has only one key

e.g

Contact.exists?("data ->> 'name' = 'Gamma'")

However my intention is to find if a contact exists with the phone number and the full json attribute specified in the same query

How can I do this?

Leonard Kakande · Accepted Answer · 2017-01-10 15:20:08Z

1

One way to accomplish it is to use where and present? instead of exists?. For example:

Contact.where("data ->> 'name' = 'Gamma' AND phone_number = '+255788778899'").present?

The other benefit of this approach is if you're going to use the collection/object if it does exist, then you can first run the query:

@contacts = Contact.where("data ->> 'name' = 'Gamma' AND phone_number = '+255788778899'")

And then later, checking for its presence won't hit the database again:

@contacts.present?

UPDATE

If you need to use variables within your query, you can do so, but be sure to wrap them in quotes. For example:

name = 'Gamma'
phone = '+255788778899'
Contact.where("data ->> 'name' = '#{name}' AND phone_number = '#{phone}'")

edited Jan 10, 2017 at 15:20

Leonard Kakande

7151 gold badge10 silver badges18 bronze badges

answered Jan 10, 2017 at 14:23

seancdavis

2,8513 gold badges28 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Leonard Kakande Over a year ago

this works great with the values hardcoded e.g data ->> 'name' = 'Gamma' However if passing a variable it fails irb(main):072:0> Contact.where("phone_number = #{@phone} AND data ->> 'name' = #{@name} AND data ->> 'region' = #{@region}") Contact Load (1.5ms) SELECT "contacts".* FROM "contacts" WHERE (phone_number = 2557812340003 AND data ->> 'name' = Gamma AND data ->> 'region' = Iringa) PG::UndefinedFunction: ERROR: operator does not exist: character varying = bigint LINE 1: SELECT "contacts".* FROM "contacts" WHERE (msisdn = 25578123... what could be the problem?

seancdavis Over a year ago

Yes, even though you may wrap the variable assignment in quotes (e.g. @name = 'Gamma', the value within the query still needs to be wrapped in quotes (e.g. '#{@name}' not just #{@name}). Updated my answer.

Dimitris Klisiaris · Accepted Answer · 2017-01-10 14:23:51Z

0

How about chaining it with a where clause like this:

Contact.where(phone_number: '+255788778899').exists?("data ->> 'name' = 'Gamma'")

answered Jan 10, 2017 at 14:23

Dimitris Klisiaris

1163 bronze badges

Collectives™ on Stack Overflow

Rails JSON query combined with other data types

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related