Rails find substring in jsonb multiple where conditions

Question

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)

You can simply cast array to text then search with [I]LIKE. e.g. SELECT * FROM (SELECT '["eggs", "with meatballs", "balls without meat"]'::text as col) foo where col ILIKE '%eat%'. Injecting that query to rails should be easy. Plz let me know if that works for you. — marmeladze
– marmeladze, Commented Oct 26, 2021 at 9:16
@spirito_libero please checkout this stackoverflow.com/a/34060269/5580690 . Hope you will find this helpful. — Nitin Srivastava
– Nitin Srivastava, Commented Oct 27, 2021 at 14:46

max · Accepted Answer · 2021-10-27 15:08:42Z

1

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.

edited Oct 27, 2021 at 15:08

answered Oct 27, 2021 at 14:33

max

103k15 gold badges113 silver badges178 bronze badges

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

Collectives™ on Stack Overflow

Rails find substring in jsonb multiple where conditions

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related