Terbium-135
Given a schema named group with another embedded schema like this:
schema "groups" do
[...]
embeds_many :absences, Absence, on_replace: :delete do
field :reason, :string
field :first_day, :date
field :last_day, :date
field :position, :integer
end
[...]
end
With the database field (as defined in a migration):
` add :absences, :map`
I.e. I get the following DB data for a single element:
[{"id": "48d308ec-e0d6-4462-a58e-95f7f47f8308", "reason": "sabbatical", "last_day": "2024-12-31", "position": 0, "first_day": "2024-01-01"}]
How I am supposed to query with ecto to exclude a group if a given date is in any element of the jsonb array?
I got stuck trying to convert the jsonb to a recordset for further usage:
from g in Backend.Schema.Group,
inner_lateral_join:
absence in fragment(
"SELECT * FROM jsonb_to_recordset(to_jsonb(?)) as absence(first_date date, last_date date)",
g.absences
),
[?]
Not asking for a solution but a few pointers in the right direction would be highly appreciated
Where Next?
Trending in Questions
Other Trending Topics
Latest Phoenix Threads
Latest on Elixir Forum
Sponsor Spotlight
Build Elixir applications with speed and confidence.
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
Build Elixir applications with speed and confidence.
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!
Master modern tools, write cleaner code, and tackle real-world challenges.
Bringing learning, work and compliance together since 1987.
Producing high quality Elixir screencasts since 2017.
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 6- Show Best Posts
- Show All (oldest first)
- Show All (newest first)
benwilson512
I realize this isn’t exactly what you’re asking but this sort of thing definitely feels like it would be better modeled by an actual table at the database level. At that point you can just use a left join between the groups and the absences, where you use the join condition to filter out the absences.
al2o3cr
+1 for “use a regular table for this”; query performance will also be much faster.
Either way (separate table for
absencesorjsonb_to_recordset), you’re looking for a “non-existence” query:WHEREto only include rows that gotNULLs from the outer-joinlinusdm
TIL this trick has a name. Cool! I use this pattern all the time.
jswanner
The name I know for this technique is “anti-join”
Terbium-135
Yes. I agree. Sounds ‘cleaner’ to me too.
That DB layout was given to me like it is and while I was looking at that JSONB field and its content in the DB I was scratching my head how to handle this.
Terbium-135
Thanks for the general layout!
I am still curious how it’s been done with that JSONB field just for learning purpose.
If I have time I will give it a try.
Looks like ecto’s embed_one, embed_many is meant to be used with the preload function
In any case, thanks all for chiming in
Creating an extra table for now