Using a lateral join to get N results from assoc per ID in list. Limit not working for inner query. What am I doing wrong?

Let’s say I have a DB With Chats, Users, Subscriptions(link between User and Chat), and User’s have an associated Avatar (which is a struct/db table, rather than a string on the user). What I want to return, for a list of chat_ids, is 5 avatars for each chat (or less if there are less subscribers).

I want a result something like this:

  chat_id1 => [
  chat_id2 => [
  chat_id3 => [],

I have a query that works, and returns exactly what I want, EXCEPT the limit function isn’t being upheld, and it’s returning an Avatar for ALL subscribers to a chat.

Here’s my current function:

from(c in Chat,
      as: :chat,
      where: in ^chat_ids,
      join: u in assoc(c, :subscribers),
      as: :user,
      join: a in assoc(u, :avatar),
        top_five in subquery(
            where: [id: parent_as(:user).id],
            order_by: [desc: :inserted_at],
            limit: 5,
            select: [:id]
      on: ==,
      group_by: [],
      select: {, fragment("json_agg(?)", a)}
    |> Repo.all()
    |> {chat_id, avatars} ->
         &struct(Avatar, &1 |> {k, v} -> {String.to_atom(k), v} end))
    |> Enum.into(%{})

Any idea what I can change to fix this? Am I going about this the wrong way with a lateral join (SO seemed to suggest this was the best way to achieve what I want, but maybe I’ve converted the SQL to Ecto incorrectly).

Thanks in advance.