Ecto query jsonb field with date ranges

Phoenix Forum Questions / Help
,
1

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

2

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.

3

+1 for “use a regular table for this”; query performance will also be much faster.

Either way (separate table for absences or jsonb_to_recordset), you’re looking for a “non-existence” query:

  • start with a query to produce only absences on the given date
  • outer-join groups to that query
  • use WHERE to only include rows that got NULLs from the outer-join
2 Likes
4

TIL this trick has a name. Cool! I use this pattern all the time.

1 Like
5

The name I know for this technique is “anti-join”

2 Likes
6

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.

1 Like
7

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

1 Like