How to check in Ruby rails, if a word is present in value of a string which is part of jsonb column in postgres?

Question

I have a User table in postgres. In rails console I search for User.where(<myWhereClause>).count

I have jsonb column called as "criteria".

Within criteria for key eligibility, I want to see if the value contains my search term 'directMBA'

eligibility is a comma separated string - 'directMBA, correspondanceMBA, onlineMBA'

This doesn't work

User.where("criteria->>'eligibility'.include?('directMBA')").count

I don't want the sql query. I need the Ruby Rails code to run in console

PS: Also, what if my keyword is 'directMBA' or 'onlineMBA'

Answer 1

You are looking for SQL LIKE operator

User.where("criteria->>'eligibility' LIKE '%directMBA%'").count

# Also, what if my keyword is 'directMBA' or 'onlineMBA'
User.where("criteria->>'eligibility' LIKE '%directMBA%' OR LIKE '%onlineMBA%'").count