Rails find substring in jsonb multiple where conditions

user_6612534 0
Viewed 87

Let's say I have the table recipes with the column ingredients, jsonb column type. Example record:

{
  id: 1,
  ingredients: [
    'eggs',
    'fragrant bread',
    'fresh tomatoes'
  ]
}

How can I retrieve the record with substring in where conditions? For example:

ingredients = ['egg', 'tomato']
Recipe.where('ingredients ?& array[:keys]', keys: ingredients)

I was trying:

ingredients = ['egg', 'tomato']
Recipe.where("ingredients @> ARRAY[?]::varchar[]", ingredients).count

But I'm getting this error:

ERROR:  operator does not exist: jsonb @> character varying[] (PG::UndefinedFunction)
jsonb activerecord ruby-on-rails sql
1 Answers

I would really consider just using two tables instead as this stinks of the unnesiccary JSON antipattern.

class Recipe
  has_many :recipe_ingredients
  has_many :recipes, through: :recipe_ingredients

  def self.with_ingredients(*ingredients)
    binds = Array.new(ingredients.length, '?')
    sql = "ingredients.name ILIKE ANY(ARRAY[#{binds.join(,)}])"
    joins(:ingredients)
      .where(
        sql,
        *ingredients.map { |i| "'%#{i}%'" }
      )
  end
end

class RecipeIngredient
  belongs_to :ingredient
  belongs_to :recipe
end

class Ingredient
  has_many :recipe_ingredients
  has_many :recipes: through: :recipe_ingredients
end

This lets you use an effective index on recipies.name, sane queries and avoids denormalization.

user_5448250
Related