How to translate this jsonb SQL query to an Active Record query?

The following data is present in my Color ActiveRecord model:

id	colored_things
1	[{"thing" : "cup", "color": "red"}, {"thing" : "car", "color": "blue"}]
2	[{"thing" : "other", "color": "green"}, {"thing" : "tile", "color": "reddish"}]
3	[{"thing" : "unknown", "color": "red"}]
4	[{"thing" : "basket", "color": "velvet or red or purple"}]

The colored_things column is defined as jsonb.

I am trying to search on all "color" keys to get values that are like a certain search term. This SQL query (see SQL Fiddle) does that:

SELECT DISTINCT C.*
FROM colors AS C,
jsonb_array_elements(colored_things) AS colorvalues(colorvalue)
WHERE colorvalue->>'color' ILIKE '%pur%';

Now I would love to translate this query to a proper Active Record query, but the below does not work:

Color.joins(jsonb_array_elements(colored_things) AS colorvalues(colorvalue))
  .where("colorvalue->>'color' ILIKE '%?%'", some_search_term)
  .distinct

This gives me error

ActiveRecord::StatementInvalid (PG::UndefinedTable: ERROR: invalid reference to FROM-clause entry for table “colors”)

Can someone point me in the proper direction?

Answer

The thing doesn’t change that much if you use ActiveRecord, and IMO it remains more readable if you leave your query as it’s in SQL.

For making it work you can use from, and there pass the raw FROM clause you currently have:

from("colors AS c, JSONB_ARRAY_ELEMENTS(colored_things) AS colorvalues(colorvalue)")

Having that your tables and aliases are already accessible, so you can chain the WHERE and SELECT clause, with their corresponding AR methods:

Color
  .from("colors AS c, JSONB_ARRAY_ELEMENTS(colored_things) AS colorvalues(colorvalue)")
  .where("colorvalue->>'color' ILIKE '%pur%'")
  .select("DISTINCT c.*")

Notice where("colorvalue->>'color' ILIKE '%?%'", value) will not work because value is quoted by the ORM, so instead you have to construct the “%value%” and use it as the bound value:

where("colorvalue->>'color' ILIKE ?", "%#{value}%")