denis
Hi, I’m running into problems when trying to interpolate somewhat “simple” json query using a fragment.
I have a jsonb column in postgres that has the following structure (array of objects):
[{"name": "test", ...}, {"name": "another", ...}, ...]
Let’s start with the SQL query that gets me what I need:
SELECT count(*)
FROM "posts" AS p0
WHERE (p0."jsonb_column" @> ANY( ARRAY [ '[{"name": "test"}]', '[{"name": "another"}]' ]::jsonb[]));
Translated to ecto (without parametrised variables) this works just fine:
from(p in Post,
where:
fragment(
"? @> ANY (ARRAY['[{\"name\":\"test\"}]','[{\"name\": \"another\"}]']::jsonb[])",
p.jsonb_column
)
)
|> Repo.aggregate(:count)
However when I use parametrised values it ends up generating a slightly different sql query, which obviously doesn’t return the same results. This is the closest I can get it:
vals = [[%{"name" => "test"}], [%{"name" => "another"}]]
from(
p in Post,
where:
fragment(
"? @> ANY (ARRAY [?]::jsonb[])",
p.jsonb_column,
^vals
)
)
|> Repo.aggregate(:count)
This generates the following SQL:
SELECT count(*) FROM "posts" AS p0 WHERE (p0."jsonb_column" @> ANY (ARRAY [$1]::jsonb[]))
with parameters: $1 = '[[{"name": "test"}], [{"name": "another"}]]'
correct parameters would be: '[{"name": "test"}]', '[{"name": "another"}]'
Any ideas about how can I get this to work? Cheers!
Trending in Announcing
WebAuthnLiveComponent WebAuthnComponents
See this post about renaming the package.
Passwordless authentication for Phoenix LiveView app...
New
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
I released Doggo, a collection of unstyled Phoenix components.
https://github.com/woylie/doggo
Features
Unstyled Phoenix components....
New
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
Hi everyone,
I’ve been working on this protobuf library for 3 years. We use it in the company I work for, EasyMile, to communicate with ...
New
Hobbes is a low-level distributed database for the Elixir programming language.
Hobbes provides a simple, safe, and scalable storage lay...
New
I’ll shortly be launching Text, a nascent text analysis library.
Current functionality
In this early version (not ready for prime time) ...
New
Other Trending Topics
I am happy to introduce the very α version of the new programming language compiled to BEAM.
Welcome Cure.
It has literally three kille...
New
Hi everyone!
The first release candidate for the Expert language server project is now available!
We’ve published a press release detai...
New
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
With AI doing more of the implementation work, I’ve been wondering how much coding I should deliberately keep doing myself.
My main conc...
New
Hey folks,
I just published a post about Hologram’s funding and where the project goes next - the short version:
Curiosum as Main Spons...
New
I love Elixir. It’s one of 2 programming languages I’ve ever fallen in love with.
But I don’t use it anymore.
Serverless was the promis...
New
Categories:
Sub Categories:
Forums
Popular Tags
- #ecto
- #liveview
- #troubleshooting
- #learning-elixir
- #library
- #deployment
- #erlang
- #testing
- #genserver
- #mix
- #absinthe
- #remote-other
- #otp
- #plug
- #how-to-question
- #macros
- #postgres
- #elixirconf
- #channels
- #exunit
- #discussion
- #code-sync
- #podcasts
- #javascript
- #onsite
- #dialyzer
- #docker
- #authentication
- #umbrella
- #full-time-contract
- #podcasts-by-brainlid
- #ai
- #ecto-query
- #elixirconf-us
- #blog-post
- #elixir-ls
- #phoenix_html
- #iex
- #graphql
- #genstage
- #websockets
- #supervisor
- #advent-of-code
- #distillery
- #processes
- #elixirconf-eu
- #api
- #forms
- #metaprogramming
- #hex










Showing Posts 1 to 2- Show Best Posts
- Show All (oldest first)
- Show All (newest first)
al2o3cr
I don’t think
ARRAYis going to do what you want -ARRAY[$1]means an array with exactly one element taken from the bind parameters.What might work better is passing the list serialized as a SQL array of JSON:
Then the consuming code looks like
? @> ANY(?::jsonb[])You may need to supply additional type information for
valsto make this work.denis
Thanks @al2o3cr, I tried this approach too by serializing values individually within a list, however it still doesn’t produce the correct results. Not sure if I’m doing it correctly though…
with fragment being:
I don’t think this is necessary, because the raw sql query works as expected without it, it’s just I can’t get ecto to do the same thing