How to query an array ecto field containing list of maps

Hey guys,

So I have an array field on my schema as field :attributes, {:array, :map}

A typical record has the results of this field as

 attributes: [
        %{"label" => "Gender", "value" => "Rooster"},
        %{"label" => "Heritage", "value" => "Lakenvelder"},
        %{"label" => "Talent", "value" => "Machete"},
        %{"label" => "Background", "value" => "Spring"},
        %{"label" => "BaseBody", "value" => "Manic Mint"},
        %{"label" => "CombColor", "value" => "Blue"},
        %{"label" => "EyesType", "value" => "Crosseyed"},
        %{"label" => "WattleColor", "value" => "Blue"}
      ],

How can I get only the results whose Heritage is in a list like this ["Lakenvelder", "Dorking", "Sultan"]

I have tried using this code but I can’t proceed from here :sweat_smile:

 defp build_heritage_query(query, heritage) do
    Enum.reduce(heritage, query, fn result, query ->
      query
      |> ???
    end)
  end

What DB do you use, how is it stored in DB, jsonb, array, varchar? Maybe it’d be possible to use SQL to get the result you need.

You can do a query like

from(q in MyModule, where: fragment("attributes @> ?", ^Jason.encode!([%{"Heritage" => "Lakenvelder"}])))
|> Repo.all()