How to query an array ecto field containing list of maps

Questions / Help
, , ,
1

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
2

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.

3

You can do a query like

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