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
The repo is at GitHub - cyberchitta/openai_ex: Community maintained Elixir library for OpenAI API · GitHub.
Docs are at OpenaiEx User Gu...
New
WebAuthnLiveComponent WebAuthnComponents
See this post about renaming the package.
Passwordless authentication for Phoenix LiveView app...
New
I fully migrated to my own harness from Anthropic/Gemini and I think it’s time to share it. Welcome DSH, the DeepSeek Harness, fully writ...
New
Beam Bots (or just BB for short) is a framework for building fault-tolerant robotics applications in Elixir using familiar OTP patterns. ...
New
Emily is an Elixir library that runs Nx computations on Apple’s MLX. Install it as the default Nx backend and Nx, defn, Axon, Nx.Serving,...
New
In 2021 I started a new library called Tempo with the objective of modelling time as a set of intervals - not as instants. In 2022 I gave...
New
Hello,
I’m sharing my plugin here in forum after some time so it has time to mature and proof yourself.
I use Claude Code daily on a pr...
New
Other Trending Topics
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
There has been a thread to discuss the Stack Overflow Developer Survey on this forum every year since 2018, so here’s yet another one for...
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 needed to vent somewhere and LinkedIn is full of hope, or hype, I’m not sure which exactly)
AI dream has many faces, but general...
New
Fly’s CEO posted this recently - Turn And Face The Strange · The Fly Blog
It says that Fly is going all-in on sprites, which is a worry ...
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
- #ai
- #umbrella
- #full-time-contract
- #podcasts-by-brainlid
- #ecto-query
- #blog-post
- #elixirconf-us
- #elixir-ls
- #phoenix_html
- #iex
- #graphql
- #genstage
- #websockets
- #supervisor
- #advent-of-code
- #distillery
- #processes
- #elixirconf-eu
- #api
- #forms
- #security
- #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