I am running into a roadblock and this SO question's second answer gets me so close!
Using postgresql I have a jsonb, places, on my orders table with the following shape:
[
{ "delivery_place" : { "id" : "abc", "name" : "my place" } },
{ "delivery_place" : { "id" : "xyz", "name" : "your place" } }
]
My goal is to use an ILIKE to query the "name" details to retrieve the correct orders. Normally I would do that with:
select id from orders, jsonb_array_elements(orders.places) as places where places -> 'delivery_place' ->> 'name' ILIKE '%my%'
I am using Rails 6 and already have a .from() clause defined and can not easily overwrite it for an optional search criteria. So I am trying to figure out how to do this with just the where clause.
The linked post above seems to get me 90% of the way there by using the @> operator. Thus I could do select id from orders where orders.places @> '["delivery_place" : {"name": "my place"}]' However I can't figure out how to combine this with an ILIKE.
Is this even possible? If not, is there a way that I am just not noticing? Trying ot use the jsonb_array_elements in the where directly gave me an error but perhaps there is a way? If not does anyone know any resources for learning how to use with clauses in Rails 6?
from orders where exists (select * from jsonb_array_elements(...) where ... ILIKE ...)