@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 %>
