Query in Ecto embeds_many association

Question

Trying to experiment with Ecto embeds_many, and work great, until I need to query on some data in the embedded field.

So I have something like a product that embeds_many categories

schema "products" do
  field :code, :string, null: false

  embeds_many :categories, Category,
    on_replace: :delete,
    primary_key: {:id, :binary_id, autogenerate: false}
  do
    field :name, :string
  end
end

def create_changeset(%Product{} = product, attrs) do
  product
  |> cast(attrs, [:code])
  |> cast_embed(:categories, with: &attributes_changeset/2)
end

def attributes_changeset(%{} = product_attribute, attrs) do
  product_attribute
  |> cast(attrs, [:id, :name])
end

After creating products I end up with something like this in the postgres table

id code categories

1    11     {"{\"id\": \"dress\", \"name\": \"Dress\"},
             "{\"id\": \"shirt\", \"name\": \"Shirt\"}}
2    22     {"{\"id\": \"dress\", \"name\": \"Dress\"}}

So now I want to query all products where id == "dress", and of course I would like to get the 2 results above.

I have experimented with something like this: q = from p in Product, where: fragment("? @> ?", p.categories, '{"id": "dress"}') but transforms the array in integers: operator does not exist: jsonb[] @> integer[] ... WHERE (p0."categories" @> ARRAY[123,34,105,100,34,58,32,34,100,114,101,115,115,34,125])

or that: q = from p in Product, where: fragment("? @> ?", p.categories, "[{\"id\": \"dress\"}]"), getting malformed array literal: "[{"id": "dress"}]"

What I hoped was something like: q = from p in Product, where: fragment("? -> 'id' = ?", p.categories, "rochie") but not sure at all if that will work.

Answer 1

Since categories is a jsonb[] here, not a plain json, the operator @> won't work with it directly. You can use ANY and <@ to do what you want:

where: fragment("? <@ ANY(?)", ~s|{"id": "dress"}|, p.categories)

This will run '{"id": "dress"}' <@ ? for each category in the categories array and return true if any of them match.

(~s|"a"| is just a cleaner way of writing "\"a\"".)