msw10100
When using postgres, I’d like to query a table where a JSONB column contains a JSON document with a field called members whose value is a list of strings. The query should return all rows where the members list in that column contains a string entry that matches a given string.
This SQL query works:
select * from typed_groups where members @> jsonb_build_object('members', jsonb_build_array('member_1'));
But I haven’t come up with an ecto query that works. I’ve tried this:
query =
from g in __MODULE__,
where:
fragment(
"? @> ?",
g.members,
^"jsonb_build_object('members', jsonb_build_array('#{member_id}'))::jsonb"
),
select: g
And I’ve tried building the interpolated string outside the query. All return no rows.
Where Next?
Trending in Questions
Other Trending Topics
Latest on Elixir Forum
Sponsor Spotlight
Courses that'll move you from confusion to "Aha, now I get it!"
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
Our Sponsors
Supporting innovation across the BEAM ecosystem.
We build reliable cloud platforms for business-critical systems.
Catch errors, track performance, monitor hosts and more.
Error tracking, logs, uptime, and Just Enough APM. Built for developers.
Elixir, Erlang & BEAM conferences from Code Sync - join us in 2026!
Practical resources that improve the lives of professional developers.
Master modern tools, write cleaner code, and tackle real-world challenges.
The deployment platform built for Elixir: PaaS ease, VPS control.
The team behind Membrane, Popcorn, LiveDebugger. Available for hire.
Courses that'll move you from confusion to "Aha, now I get it!"
Showing Posts 1 to 5- Show Best Posts
- Show All (oldest first)
- Show All (newest first)
LostKobrakai
You need a fragment for the
jsonb_build_objectandjsonb_build_arrayas well.msw10100
How would that look? since those are inside a fragment themselves?
LostKobrakai
You can either use one big fragment, where you put the whole sql in one string and have more parameters or you can also provide nested fragments as parameters of an outer fragment.
The latter is useful because you can wrap fragment usage in macros and essentually extend the available APIs to use in query building: Ecto.Query.API — Ecto v3.11.2
msw10100
Cool, thanks!! Both of those suggestions work great if I put in a literal string for member_id in that last fragment. If I pin it to a variable with ^member_id instead of a literal string, I get an indeterminate_datatype error.
To fix this, I had to add ::text after the final ? as a type hint.
LostKobrakai
That’s where postgres can no longer infer the type, because you started to use a paramter. You can use Ecto.Query.API — Ecto v3.11.2 to force a type.