How to query on keys in an embeds_many

Given the following schema:

  schema "invoices" do
    embeds_many :items, InvoiceItem


And the embedded schema

  embedded_schema do
    field :name, :string
    field :amount, :integer
    field :price, :integer

How can I query for items with a given name? I can’t seem to get it working.

I tried fragments, joins, apparently lateral joins aren’t supported directly but can be used in a different form using subqueries, but I’m afraid I don’t see how.

Can anyone shed some light? This can’t be such an obscure usecase

Have you tried something like this?

query =
  from invoices in Invoice,
      items in fragment(
        "SELECT * FROM jsonb_to_recordset(to_jsonb(?)) as items(name text, amount integer, price integer)",
    select: %InvoiceItem{name:, amount: items.amount, price: items.price},
    where: == ^"Horse"

I was unaware that inner_lateral_join was supported in Ecto.
I managed to get it working! Thanks