Complex queries on nested maps using match spec

[sheharyarn]

A question was posted on the ElixirForum today:

I’m using @sheharyarn’s Memento for integrating Mnesia for my Phoenix App. Having little to no experience in erlang, where should I look in the documentation for complex queries, I am storing a map in one of my column and I need to fetch by querying in that map, I can do this in Ecto using fragments but I’m wondering how to do the same using Mnesia.

The Erlang Matchspec is very confusing, especially so for beginners. It becomes even harder to write them when nested maps are involved. Quite often, I also forget how to use them for many scenarios and have to read the Erlang docs on match_spec again.

This was one of the very reasons I decided to write the Memento library in the first place. While I won't add support for an API to directly query nested maps in the package as of yet, I'll use this issue to cover some of the advanced queries for now.

[sheharyarn]

Use this Table and dataset for the examples covered below:

defmodule Nested do
  use Memento.Table, type: :ordered_set, attributes: [:id, :data]

  def seed do
    nested = [
      %Nested{id: 1, data: %{title: "Elixir",  type: :language,  stars: 15000}},
      %Nested{id: 2, data: %{title: "Phoenix", type: :framework, stars: 13000}},
      %Nested{id: 3, data: %{title: "Memento", type: :library,   stars: 160}},
      %Nested{id: 4, data: %{title: "Ecto",    type: :library,   stars: 4000}},
      %Nested{id: 5, data: %{title: "Ruby",    type: :language,  stars: 16000}},
    ]

    Memento.transaction(fn -> Enum.each(nested, &Memento.Query.write/1) end)
  end
end

Create and seed it:

Memento.Table.create(Nested)
Nested.seed()

---

Note: All of the examples below need to be run inside a Memento.Transaction but are omitted in the examples for brevity and clarity.

[sheharyarn]

Checking equality using Query.match/3

If you just want to return all records where an attribute of the nested map has a specific value, the easiest option is to use Query.match/3. This will return all records where the second attribute (i.e. the data map) matches a map where :type is set to :language:

Query.match(Nested, {:_, %{type: :language}})
# => [
#  %Nested{id: 1, data: %{title: "Elixir", type: :language, stars: 15000}},
#  %Nested{id: 5, data: %{title: "Ruby", type: :language, stars: 16000}},
#]

[sheharyarn]

Checking equality using Query.select_raw/3

Query.select_raw/3 offers more control but requires you to write the full erlang match spec. The same query from above (to get all records where type is :language) can be written like this:

# Assign a match variable to each key and attribute
match_head = {Nested, :"$1", %{title: :"$2", type: :"$3", stars: :"$4"}} 

# Define guards where type is set to :language
guards = [{:==, :"$3", :language}]

# Return all records for this query
Query.select_raw(Nested, [{ match_head, guards, [:"$_"] }])

If you replace the [:"$_"] part with [:"$2"], it'll return the titles of the matched records (you also need to disable coercion):

Query.select_raw(Nested, [{ match_head, guards, [:"$2"] }], coerce: false)
# => ["Elixir", "Ruby"]

[sheharyarn]

Using more operators with Query.select_raw/3

---

Get the titles of all projects which have more than 10k stars:

result = [:"$2"]
guards = [{:>=, :"$4", 10_000}]

Query.select_raw(Nested, [{ match_head, guards, result }], coerce: false)
# => ["Elixir", "Phoenix", "Ruby"]

---

Get all the records which have stars between 100 and 1000:

result = [:"$_"]
guards = [
  {:>=, :"$4", 100},
  {:"=<", :"$4", 1000}
]

Query.select_raw(Nested, [{ match_head, guards, result }])
# => [%Nested{id: 3, data: %{title: "Memento", type: :library, stars: 160}}]

---

Get the type of the projects whose id is either 2 or 4:

result = [:"$3"]
guards = [
  {:orelse,
    {:==, :"$1", 2},
    {:==, :"$1", 4},
  }
]

Query.select_raw(Nested, [{ match_head, guards, result }], coerce: false)
# => [:framework, :library]

---

Get the records which have 1000 times the number of stars of its id:

result = [:"$_"]
guards = [
  {:==, :"$4",
    {:*, :"$1", 1000},
  }
]

Query.select_raw(Nested, [{ match_head, guards, result }])
# => [%Nested{id: 4, data: %{title: "Ecto", type: :library, stars: 4000}}]