How to query for empty array in jsonb PostgreSQL using Elixir / Ecto

Question

I have an Ecto schema with embeds_many defined like this:

  schema "rounds" do
    embeds_many :growth_cycles, SomeModule.GrowthCycle, on_replace: :delete
  end

This translates to a jsonb field in PostgreSQL. The default value is an empty array - []. I'd like to write an Ecto query that returns only Rounds that have growth_cycles = [] (growth_cycles are not set/empty).

The simplest thing I tried was:

    from(r in Round, where: r.growth_cycles == [])

But this gives the following error:

** (Postgrex.Error) ERROR 42P18 (indeterminate_datatype) cannot determine type of empty array
...
hint: Explicitly cast to the desired type, for example ARRAY[]::integer[].

I've also tried:

    from(r in Round, where: length(r.growth_cycles) == 0)

But this gives an error saying that length isn't a valid query expression.

I see references to using fragments to drop down to raw PostgreSQL, but I'm not sure how to do this.

Answer 1

You can try using fragment/1 to interject raw SQL into your queries.

In this case, something like

(from r in Round, where: fragment("? = '{}'", r.growth_cycles)) |> Repo.all

should work

From the documentation:

It is not possible to represent all possible database queries using Ecto's query syntax. When such is required, it is possible to use fragments to send any expression to the database: