Search code examples
sqlelixirecto

Ecto query with Where matcher based on nested map key-value pair

I have the following record structure:

%Event{
  external_id: 379, 
  type: "abc"
  fields: %{
    "status" => "accepted",
    "other_field" => "123"
}

How do I go about creating a query that I can only return Events where the nested "status" == "accepted"? Do I have to resort to using raw SQL commands, or is there a simple way?

Solution

  • There is no need to resort to raw SQL commands, since Ecto provides query DSL. Compared to other languages you shouldn't worry about writing queries, since by default they get sanitized.

    There are 2 approaches to building queries:

    1. Keyword-based syntax (less verbose, however expressions cannot be piped together)
    2. Pipe-based syntax (allows piping of expression, used for more complex expressions)

    Keyword-based query:

    from e in Event,
  where: e.fields["status"] == "accepted"

    where Event is your schema.

    Pipe-based query:

    "events"
|> where([e], e.fields["status"] == "accepted")

    where events is the table name.