Converting a nested keyword list into a keyword map

Questions / Help
1

I have a function

def list_store_params do
  #query = Ecto.Query.from(s in Store, select: [s.state, s.city, s.name], distinct: true)
  IO.inspect(Repo.all(query()))
  query()
  |> Repo.all()
  |> Enum.group_by(&Enum.take(&1, 2))
  |> Enum.map(fn {key, value} ->
    key ++ Enum.flat_map(value, &Enum.drop(&1, 2))
    end)
  |> Enum.chunk_every(2)
  |> IO.inspect
end  

def query,
do:
  from(s in Store,
    order_by: [asc: s.state, asc: s.city, asc: s.name],
    select: %{
        a: s.state,
        b: s.city,
        c: s.name
    }
  )

that converts

[
%{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"}
 ]

into

[
  [
    [a: "Idaho", b: "Boise", c: "Test"],
    [a: "Colorado", b: "Denver", c: "Eagle", c: "Jelly Beans", c: "Name"]
  ]
]

which I need to convert to a keyword map {a: “Idaho”, b: “Boise”, c: “Test”}. I’m having trouble understanding Enum.into and Map.new, does anyone have any direction/sources here?

2

You shouldn’t be using Enum.take/2 or Enum.drop/2 on a Map because the key order is not guaranteed - it’s considered an implementation detail. You should choose a more reliable means.

iex(1)> Map.to_list(%{a: "Idaho", b: "Boise", c: "Test"}) 
[a: "Idaho", b: "Boise", c: "Test"]
iex(2)> %{a: "Idaho", b: "Boise", c: "Test"} |> Map.to_list() |> Keyword.take([:a,:b])
[a: "Idaho", b: "Boise"]
iex(3)> %{a: "Idaho", b: "Boise", c: "Test"} |> Map.to_list() |> Keyword.take([:c])   
[c: "Test"]
iex(4)>

Note also that Enum.group_by/3 also accepts a value_fun/1 as the third parameter, so you could extract the :c pair immediately. Then you can use List.flatten/1 in the next step.

iex(1)> Map.new([a: "Idaho", b: "Boise", c: "Test"])
%{a: "Idaho", b: "Boise", c: "Test"}
iex(2)>

It also isn’t clear from your description what exactly you want to accomplish. Given:

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

what exactly, in detail is output from output = convert(input) going to look like?

3

I grouped your list by :a and :b keys, then I merged all the :c keys, mapped into a keyword list.

I am not sure if this is what You expect, but it seems to output the right result (minus the fact You wrap all in a double list …).

iex(1)> list = [
%{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"}
 ]
iex(2)> list |>                                                                                                                                                                 
...(2)> Enum.group_by(fn el -> {el.a, el.b} end) |>                                                                                                                             
...(2)> Enum.map(fn {{a, b}, v} ->
...(2)> Enum.reduce(v, [a: a, b: b], fn %{c: c}, acc ->
...(2)> Keyword.merge(acc, [{:c, c} | Keyword.get_values(acc, :c) |> Enum.map(& {:c, &1})])
...(2)> end)
...(2)> end)
[
  [a: "Colorado", b: "Denver", c: "Name", c: "Jelly Beans", c: "Eagle"],
  [a: "Idaho", b: "Boise", c: "Test"]
]

It’s kind of ugly code :slight_smile:

I would prefer something like this as I don’t like to have duplicate keys.

[a: "Colorado", b: "Denver", c: ["Name", "Jelly Beans", "Eagle"]]
4

I think I should probably be accomplishing what I need through the query in the first place rather than doing math on the whole database client side. The idea is to group c in b and b in a.

I want to show a distinct list of states, with a distinct list of cities with a list of all entries of c.

it should look like diagram

1 Like
5

Yeah, I also think that’s best solution.

I do not have repository to test this code, but it should look like:

alias Ecto.Query
require Query

Store
|> Query.from(as: :store)
|> Query.group_by([store: store], [store.state, store.city])
|> Query.select([store: store], %{
  a: store.state,
  b: store.city,
  c: fragment("array_agg(?)", store.name)
})
1 Like
6

returns # Tuples can only be used in comparisons with literal tuples of the same size

7

right, it should be List - not Tuple

1 Like
8

That returns:

[
  %{a: "Ohio", b: "Columbus", c: ["CMH", "Jelly Beans", "ABC"]},
  %{a: "Idaho", b: "Boise", c: ["Test"]},
  %{a: "Ohio", b: "Cleveland", c: ["different city"]}
]

is there a way to make a: distinct like

%{a: "Ohio", b: ["Columbus", "Cleveland"], c: ["CMH", "Jelly Beans", 
 "ABC", "different city"]

I’m trying to accomplish diagram

9

sure, just change a bit query:

alias Ecto.Query
require Query

Store
|> Query.from(as: :store)
|> Query.group_by([store: store], store.state)
|> Query.select([store: store], [
  a: store.state,
  b: fragment("array_agg(?)", store.city),
  c: fragment("array_agg(?)", store.name)
])
1 Like
10

As I have more than one entry under a city, this doesn’t accomplish what I was hoping either.

[
  %{
    a: "Ohio",
    b: ["Columbus", "Columbus", "Columbus", "Cleveland"],
    c: ["CMH", "Jelly Beans", "ABC", "different city"]
  },
  %{a: "Idaho", b: ["Boise"], c: ["Test"]}
]

I want to display it in a template like

<%= stores <- @stores do %>
  <%= stores.a %>
  <%= stores.b %>
  <%= stores.c %>
<% end %>

etc to solve an accordion like flow as in diagram

Thanks for your help.

11

The old school approach is to have the RDBMS order the result to keep the necessary information clustered together and then aggregate the result rows:

# file: music_db/priv/repo/playground.exs
#
# http://www.pragmaticprogrammer.com/titles/wmecto
# https://pragprog.com/titles/wmecto/source_code
# http://media.pragprog.com/titles/wmecto/code/wmecto-code.zip
#
# pg_ctl -D /usr/local/var/postgres start
# mix format ./priv/repo/playground.exs
# mix run ./priv/repo/playground.exs
#

defmodule AppInfo do
  def string() do
    Application.loaded_applications()
    |> Enum.map(&to_app_keyword/1)
    |> Enum.sort_by(&map_app_name/1)
    |> Enum.map_join(", ", &app_keyword_to_string/1)
  end

  defp to_app_keyword({app, _, vsn}),
    do: {app, vsn}

  defp app_keyword_to_string({app, vsn}),
    do: Atom.to_string(app) <> ": " <> to_string(vsn)

  defp map_app_name({app, _}),
    do: app
end

defmodule Playground do
  import Ecto.Query
  alias MusicDB.Repo
  alias MusicDB.{Artist, Album, Track}

  def query,
    do:
      from(a in Artist,
        left_join: b in Album,
        on: a.id == b.artist_id,
        left_join: t in Track,
        on: b.id == t.album_id,
        order_by: [asc: a.id, asc: b.id, asc: t.index],
        select: %{
          artist_id: a.id,
          name: a.name,
          album_id: b.id,
          album_title: b.title,
          track_id: t.id,
          title: t.title
        }
      )

  def init_acc(),
    do: {{nil, nil, []}, {nil, nil, []}, []}

  def init_album_acc(%{album_id: album_id, album_title: album_title, title: title}),
    do: {album_id, album_title, [title]}

  def aggregate_album_acc(track, {album_id, title, tracks}),
    do: {album_id, title, [track.title | tracks]}

  def init_artist_acc(%{artist_id: artist_id, name: name}),
    do: {artist_id, name, []}

  def aggregate_artist_acc({_, title, tracks}, {artist_id, name, albums}),
    do: {artist_id, name, [{title, :lists.reverse(tracks)} | albums]}

  def aggregate_artists({{nil, _, _}, {nil, _, _}, []}) do
    []
  end

  def aggregate_artists({album_acc, artist_acc, artists}) do
    {_, name, albums} = aggregate_artist_acc(album_acc, artist_acc)
    [{name, :lists.reverse(albums)} | artists]
  end

  # same album and artist - just add a track
  def aggregate(
        %{artist_id: artist_id, album_id: album_id} = track,
        {{album_id, _, _} = acc, {artist_id, _, _} = artist_acc, artists}
      ),
      do: {aggregate_album_acc(track, acc), artist_acc, artists}

  # same artist, new album
  def aggregate(
        %{artist_id: artist_id} = track,
        {album_acc, {artist_id, _, _} = artist_acc, artists}
      ),
      do: {init_album_acc(track), aggregate_artist_acc(album_acc, artist_acc), artists}

  # new artist and album
  def aggregate(track, acc),
    do: {init_album_acc(track), init_artist_acc(track), aggregate_artists(acc)}

  def play do
    IO.puts(AppInfo.string())

    query()
    |> Repo.all()
    |> List.foldl(init_acc(), &aggregate/2)
    |> aggregate_artists()
    |> :lists.reverse()
  end
end

IO.inspect(Playground.play())

$ mix run ./priv/repo/playground.exs
asn1: 5.0.7, compiler: 7.2.7, connection: 1.0.4, crypto: 4.3.3, db_connection: 2.0.3, decimal: 1.6.0, ecto: 3.0.5, ecto_sql: 3.0.3, elixir: 1.7.4, hex: 0.18.2, inets: 7.0.2, kernel: 6.1.1, logger: 1.7.4, mix: 1.7.4, music_db: 0.1.0, poison: 3.1.0, postgrex: 0.14.1, public_key: 1.6.3, ssl: 9.0.3, stdlib: 3.6, telemetry: 0.2.0

23:46:42.849 [debug] QUERY OK source="artists" db=1.3ms decode=0.6ms queue=1.3ms
SELECT a0."id", a0."name", a1."id", a1."title", t2."id", t2."title" FROM "artists" AS a0 LEFT OUTER JOIN "albums" AS a1 ON a0."id" = a1."artist_id" LEFT OUTER JOIN "tracks" AS t2 ON a1."id" = t2."album_id" ORDER BY a0."id", a1."id", t2."index" []
[
  {"Miles Davis",
   [
     {"Kind Of Blue",
      ["So What", "Freddie Freeloader", "Blue In Green", "All Blues",
       "Flamenco Sketches"]},
     {"Cookin' At The Plugged Nickel",
      ["If I Were A Bell", "Stella By Starlight", "Walkin'", "Miles",
       "No Blues"]}
   ]},
  {"Bill Evans",
   [
     {"You Must Believe In Spring",
      ["B Minor Waltz (for Ellaine)", "You Must Believe In Spring",
       "Gary's Theme", "We Will Meet Again (for Harry)", "The Peacocks",
       "Sometime Ago", "Theme From M*A*S*H (Suicide Is Painless)",
       "Without a Song", "Freddie Freeloader", "All of You"]},
     {"Portrait In Jazz",
      ["Come Rain Or Come Shine", "Autumn Leaves", "Witchcraft",
       "When I Fall In Love", "Peri's Scope", "What Is This Thing Called Love?",
       "Spring Is Here", "Someday My Prince Will Come", "Blue In Green"]}
   ]},
  {"Bobby Hutcherson",
   [
     {"Live At Montreaux",
      ["Anton's Ball", "The Moontrane", "Farallone", "Song Of Songs"]}
   ]}
]
$

Using Ecto associations is the easier option.

# file: music_db/priv/repo/playground.exs
#
# http://www.pragmaticprogrammer.com/titles/wmecto
# https://pragprog.com/titles/wmecto/source_code
# http://media.pragprog.com/titles/wmecto/code/wmecto-code.zip
#
# pg_ctl -D /usr/local/var/postgres start
# mix format ./priv/repo/playground.exs
# mix run ./priv/repo/playground.exs
#

defmodule AppInfo do
  def string() do
    Application.loaded_applications()
    |> Enum.map(&to_app_keyword/1)
    |> Enum.sort_by(&map_app_name/1)
    |> Enum.map_join(", ", &app_keyword_to_string/1)
  end

  defp to_app_keyword({app, _, vsn}),
    do: {app, vsn}

  defp app_keyword_to_string({app, vsn}),
    do: Atom.to_string(app) <> ": " <> to_string(vsn)

  defp map_app_name({app, _}),
    do: app
end

defmodule Playground do
  import Ecto.Query
  alias MusicDB.Repo
  alias MusicDB.{Artist}

  def query,
    do:
      from(a in Artist,
        left_join: m in assoc(a, :albums),
        left_join: t in assoc(m, :tracks),
        order_by: [asc: a.id, asc: m.id, asc: t.index],
        preload: [albums: {m, tracks: t}]
      )

  def play do
    IO.puts(AppInfo.string())

    artists =
      query()
      |> Repo.all()

    for(
      %MusicDB.Artist{name: name, albums: albums} <- artists,
      do:
        {name,
         for(
           %MusicDB.Album{title: title, tracks: tracks} <- albums,
           do: {title, [for(%MusicDB.Track{title: title} <- tracks, do: title)]}
         )}
    )
  end
end

IO.inspect(Playground.play())

$ mix run ./priv/repo/playground.exs
asn1: 5.0.7, compiler: 7.2.7, connection: 1.0.4, crypto: 4.3.3, db_connection: 2.0.3, decimal: 1.6.0, ecto: 3.0.5, ecto_sql: 3.0.3, elixir: 1.7.4, hex: 0.18.2, inets: 7.0.2, kernel: 6.1.1, logger: 1.7.4, mix: 1.7.4, music_db: 0.1.0, poison: 3.1.0, postgrex: 0.14.1, public_key: 1.6.3, ssl: 9.0.3, stdlib: 3.6, telemetry: 0.2.0

00:28:32.879 [debug] QUERY OK source="artists" db=5.0ms decode=0.6ms queue=1.6ms
SELECT a0."id", a0."name", a0."birth_date", a0."death_date", a0."inserted_at", a0."updated_at", a1."id", a1."title", a1."inserted_at", a1."updated_at", a1."artist_id", t2."id", t2."title", t2."duration", t2."index", t2."number_of_plays", t2."inserted_at", t2."updated_at", t2."album_id" FROM "artists" AS a0 LEFT OUTER JOIN "albums" AS a1 ON a1."artist_id" = a0."id" LEFT OUTER JOIN "tracks" AS t2 ON t2."album_id" = a1."id" ORDER BY a0."id", a1."id", t2."index" []
[
  {"Miles Davis",
   [
     {"Kind Of Blue",
      [
        ["So What", "Freddie Freeloader", "Blue In Green", "All Blues",
         "Flamenco Sketches"]
      ]},
     {"Cookin' At The Plugged Nickel",
      [
        ["If I Were A Bell", "Stella By Starlight", "Walkin'", "Miles",
         "No Blues"]
      ]}
   ]},
  {"Bill Evans",
   [
     {"You Must Believe In Spring",
      [
        ["B Minor Waltz (for Ellaine)", "You Must Believe In Spring",
         "Gary's Theme", "We Will Meet Again (for Harry)", "The Peacocks",
         "Sometime Ago", "Theme From M*A*S*H (Suicide Is Painless)",
         "Without a Song", "Freddie Freeloader", "All of You"]
      ]},
     {"Portrait In Jazz",
      [
        ["Come Rain Or Come Shine", "Autumn Leaves", "Witchcraft",
         "When I Fall In Love", "Peri's Scope",
         "What Is This Thing Called Love?", "Spring Is Here",
         "Someday My Prince Will Come", "Blue In Green"]
      ]}
   ]},
  {"Bobby Hutcherson",
   [
     {"Live At Montreaux",
      [["Anton's Ball", "The Moontrane", "Farallone", "Song Of Songs"]]}
   ]}
]
$

Given the following schemas

  schema "artists" do
    field(:name)
    field(:birth_date, :date)
    field(:death_date, :date)
    timestamps()

    has_many(:albums, Album)
    has_many(:tracks, through: [:albums, :tracks])
  end

  schema "albums" do
    field(:title, :string)
    timestamps()

    belongs_to(:artist, Artist)
    has_many(:tracks, Track)
    many_to_many(:genres, Genre, join_through: "albums_genres")
  end

  schema "tracks" do
    field(:title, :string)
    field(:duration, :integer)
    field(:duration_string, :string, virtual: true)
    field(:index, :integer)
    field(:number_of_plays, :integer)
    timestamps()

    belongs_to(:album, Album)
  end
1 Like
12

@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 %>
2 Likes
13

For some reason, using the special version, c: value is coming back wrapped in some extra brackets, for me.

[
  %{a: "Idaho", b: ["Boise"], c: ["Test"]},
  %{a: "Ohio", b: ["Cleveland"], c: ["different city"]},
  %{a: "Ohio", b: ["Columbus"], c: ["CMH", "Jelly Beans", "Nathan Caldwell"]}
]

Gets converted to

%{
  "Idaho" => %{["Boise"] => [["Test"]]},
  "Ohio" => %{
    ["Cleveland"] => [["different city"]],
    ["Columbus"] => [["CMH", "Jelly Beans", "Nathan Caldwell"]]
  }
}

giving me the error protocol Phoenix.Param not implemented for ["Test"].

when trying to create a link to: <%= link name, to: Routes.page_path(@conn, :show, name) %>

14

It’s because your input have List where it should have String.

Example input which you show me and with witch my code works is:

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"}
]
15

Yeah I edited the query a bit to get it to return what I wanted like so:

Store
    |> Query.from(as: :store)
    |> Query.group_by([store: store], [store.state, store.city])
    |> Query.select([store: store], %{
        a: store.state,
        b: fragment("array_agg( DISTINCT ?)", store.city),
        c: fragment("array_agg(?)", store.name)
        })
    |> Repo.all

is there a way to edit do_sample/2 to remove the extra list bracket?

<%= for [name] <- names do %> works for an example of having only one store, but if there’s multiple stores nothing shows up.

fixed with: <%= for {city, [names]} <- cities do %>

Thank you so much for your help, I’ve learned a ton from everyone.