silviurosu

silviurosu

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.

Showing Posts 1 to 4

mechabyte

mechabyte

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

silviurosu

silviurosu OP

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.

ewout

ewout

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

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

silviurosu OP

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.

— All posts loaded —

Where Next? Top

Trending in Questions Top

katta
I having some trouble figuring out if I have set myself too strict of standards for my production server. Currently I can handle 75% of r...
New
brecabral
Documentation While reading the Scoped Routes section, I noticed that the documentation currently refers to a problem without explainin...
New
nseaSeb
Hello, I know there is an approach for handling lists that allows for optimized traversal, but I can’t recall the specific method (somet...
New
kpanic
Hi everyone, I am toying with the idea of building a “match maker” for giving personal help to people that wants to start coding. I sta...
New
velrest
So my question is quite simple and i have found no conclusive answer on forum, google or AI. Should we use :erlang.float for Integer to ...
New
asweet-confluent
I recently noticed that Elixir’s Logger defaults its primary log level to :debug when no :logger, :level application configuration is pre...
New
apz
I’m new to elixir and just tried to install the elixirLS extension for VScode(ium) and it is throwing some errors that I would like help ...
New

Other Trending Topics Top

GenericJam
Edit: 2026 May 15 - This post is archived. Mob is alive!! Main docs: mob v0.7.11 — Documentation A bit of explanation for the slightly c...
New
JesseHerrick
Hey, I’m Jesse and I’m the main contributor behind Dexter, a full-featured, lightning-fast Elixir LSP optimized for large codebases. It s...
New
mudasobwa
I am happy to introduce the very α version of the new programming language compiled to BEAM. Welcome Cure. It has literally three kille...
New
garrison
Hobbes is a low-level distributed database for the Elixir programming language. Hobbes provides a simple, safe, and scalable storage lay...
New
mhanberg
Hi everyone! The first release candidate for the Expert language server project is now available! We’ve published a press release detai...
New
budgie
A little off-topic, but I feel like people here have a good head on their shoulders. I used to be quite good at making software. Was luc...
New

We're in Beta

About us Mission Statement

Options

Thread Display Mode




Thread Preview

Skip Thread Previews