msw10100
Ecto query of a JSONB column
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.
Trending in Questions
Other Trending Topics
Latest on Elixir Forum
Sponsor Spotlight
Producing high quality Elixir screencasts since 2017.
Categories:
Sub Categories:
Forums
Popular Tags
- #ecto
- #liveview
- #troubleshooting
- #learning-elixir
- #deployment
- #library
- #erlang
- #testing
- #genserver
- #mix
- #absinthe
- #remote-other
- #otp
- #plug
- #how-to-question
- #macros
- #postgres
- #channels
- #elixirconf
- #exunit
- #discussion
- #code-sync
- #javascript
- #podcasts
- #onsite
- #dialyzer
- #docker
- #authentication
- #umbrella
- #full-time-contract
- #podcasts-by-brainlid
- #ecto-query
- #elixir-ls
- #phoenix_html
- #iex
- #blog-post
- #graphql
- #genstage
- #ai
- #elixirconf-us
- #websockets
- #supervisor
- #advent-of-code
- #distillery
- #processes
- #api
- #forms
- #metaprogramming
- #hex
- #performance
Our Sponsors
Build Elixir applications with speed and confidence.
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.
Bringing learning, work and compliance together since 1987.
Producing high quality Elixir screencasts since 2017.
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!"
First 5 of 5 Posts
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.