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
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
Following on from my CLDR lbraries I started work on Unicode transforms. But like everything related to CLDR there is a lot of yak-shavin...
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
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
Hey. Is there anyone here who creates agents in their apps? Not talking about using agents, but creating them. I’m finding it pretty diff...
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
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
I just stumbled on a newly redesigned elixir-lang.org. :tada: It looks like @Software_Mansion did the work, and I think it is generally a...
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 2 to 1- Show Best Posts
- Show All (oldest first)
- Show All (newest first)
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
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.