@Codball: Sorry for delay …

First of all for that you do not need any arrays in a, b and c key. You only need to iterate by all records from database.

If for some reason you still want to have a nested struct then I guess that you would need something like:

%{
  "Colorado" => %{"Denver" => ["Eagle", "Jelly Beans", "Name"]},
  "Idaho" => %{"Boise" => ["Test"]}
}

To achieve that in pure SQL query we would need to use lateral joins and some jsonb functions. Here is example code:

defmodule Example do
  alias Ecto.Query
  alias Example.{Repo, Store}

  require Query

  def sample do
    query =
      Store
      |> Query.from(as: :store1)
      |> Query.join(
        :inner_lateral,
        [store1: store1],
        store2 in fragment(
          """
          select jsonb_object_agg(store2.city, store3.data) as data
              from stores store2
              inner join lateral (
                select array_agg(name) as data
                  from stores store3
                  where store2.state = store3.state and store2.city = store3.city
              ) store3 on true
              where store2.state = ?
          """,
          store1.state
        ),
        as: :store2
      )
      |> Query.select(
        [store1: store1, store2: store2],
        fragment(
          "jsonb_object_agg(?, ?)",
          store1.state,
          store2.data
        )
      )

    Repo.one!(query)
  end
end

Here is alternative version for inner join:

defmodule Example do
  alias Ecto.Query
  alias Example.{Repo, Store}

  require Query

  def sample do
    store3_query =
      Store
      |> Query.from(as: :store3)
      |> Query.group_by([store3: store3], [store3.city, store3.state])
      |> Query.select([store3: store3], %{
        city: store3.city,
        data: fragment("array_agg(?)", store3.name),
        state: store3.state
      })
      |> Query.subquery()

    store2_query =
      Store
      |> Query.from(as: :store2)
      |> Query.group_by([store2: store2], store2.state)
      |> Query.join(
        :inner,
        [store2: store2],
        store3 in ^store3_query,
        as: :store3,
        on: store2.city == store3.city and store2.state == store3.state
      )
      |> Query.select(
        [store2: store2, store3: store3],
        %{data: fragment("jsonb_object_agg(?, ?)", store2.city, store3.data), state: store2.state}
      )
      |> Query.subquery()

    query =
      Store
      |> Query.from(as: :store1)
      |> Query.join(
        :inner,
        [store1: store1],
        store2 in ^store2_query,
        as: :store2,
        on: store1.state == store2.state
      )
      |> Query.select(
        [store1: store1, store2: store2],
        fragment(
          "jsonb_object_agg(?, ?)",
          store1.state,
          store2.data
        )
      )

    Repo.one!(query)
  end
end

I’m not sure, but it should be slower in benchmarks than first one, but at least looks much more nice.

Running Example.sample would return previously pasted sample output.

Here is special version if you do not want to do it on database level (due to ecto problems with lateral joins where we need to use big fragments blobs).

defmodule Example do
  def sample(input), do: Enum.reduce(input, %{}, &do_sample/2)

  defp do_sample(%{a: a, b: b, c: c}, acc),
    do: update_in(acc, [Access.key(a, %{}), Access.key(b, [])], &[c | &1 || []])
end

input = [
  %{a: "Idaho", b: "Boise", c: "Test"},
  %{a: "Colorado", b: "Denver", c: "Eagle"},
  %{a: "Colorado", b: "Denver", c: "Jelly Beans"},
  %{a: "Colorado", b: "Denver", c: "Name"}
]

Example.sample(input)
%{
  "Colorado" => %{"Denver" => ["Name", "Jelly Beans", "Eagle"]},
  "Idaho" => %{"Boise" => ["Test"]}
}

In order to use any of those function returns in template you would need to:

<%= for {state, cities} <- @data do %>
  <%= for {city, names} <- cities do %>
    <%= for name <- names do %>
      <%= state %>
      <%= cite %>
      <%= name %>
    <% end %>
  <% end %>
<% end %>