I am trying to build (let’s say) a bit more complex queries on the data blob (JSON object). Resulting queries are supposed to look like:
SELECT * FROM items WHERE data->>'price' ilike '%14%' OR data->>'title' ilike '%pi%';
Ideally with a large amount of AND and OR blocks
The thing is that in order to translate it into Ecto I have to use fragments:
Item |> where([i], fragment("data->>? ILIKE ?", ^key, ^value))
It looks like that the number of
? defines the number of arguments I have to pass into the fragment function. And I don’t understand if there is a way to make it more dynamic? E.g. if I want to combine a given number of fragments, how would I do it?
As far as I see if I will do:
Item |> where([i], fragment("data->>? ILIKE ?", ^key, ^value)) |> where([i], fragment("data->>? ILIKE ?", ^key1, ^value1))
I will get AND instead of OR in the resulting query.