Ecto JSONB array 'contains' query

Questions / Help
,
1

I am facing an issues creating a query to exclude results if a jsonb array field contains specified value.
This works in plain sql and I am trying to generate it from Ecto:

If I run plain sql it works though:

AND (not o0.“exclusions” @> ‘[83055]’)

I tried more ideas including this two:

where: fragment(“not ? @> ‘[?]’”, c.exclusions, ^dish_id)

It generates this sql:

(not o0.“exclusions” @> ‘[$2]’)

This does not run:

** (Postgrex.Error) ERROR 22P02 (invalid_text_representation): invalid input syntax for type json

value = “‘[#{dish_id}]’”
where: fragment(“not ? @> ?”, c.exclusions, ^value)

This generates:

(not o0.“exclusions” @> $2) [10318, “‘[83055]’”]

It does not raise any error but the filter is not working.

1 Like
2

Have you explored using raw SQL queries using Ecto? Here’s a relevant section in their docs, as well a related Medium article.

3

My query is pretty complex. I would not move this to plain sql since I would need to map the results back to structs. And just for this small issues to move all to plain sql does not sound like a good idea to me.

4

We ran into the same problem and we solved it like this:

where: fragment("? @> ?::jsonb", c.exclusions, ^[dish_id])
9 Likes
5

It worked :). I lost a day struggling with this thing. You saved me.
We need more documentation regarding query on jsonb fields in Ecto. Maybe I’ll write some blog posts.

2 Likes