Optional fields in Ecto - How to define it


When we define a field in Ecto migration script like so:

  add_if_not_exists(:metadata, :jsonb)

How to make this field optional? Meaning the field need not be provided in INSERT. What would the value be when not provided. The type is jsonb?


It is optional by default. If you make it null: false then it is not optional. The value would be NULL when not provided.

1 Like

Thanks Ben!

Not sure if this is the right thing to do, but I have a follow-up question:

When a field metadata is defined as jsonb, which is essentially a Map, and is used to hold any key/val pairs, how would you do a query to retreive based on fields in the map?

Lets say table fh contains 3 field: id, status and metadata. I have queries in ecto which can get records based on id and status. I want to write a query based on exact match on id, status and metadata?
The metadata field can contain elements like this:

   "a" => "12345",

Another record can have metadata like this:

   "a" => "7890",

When I query for exact match on all fields ,
How would I retrieve using ecto the exact metadata. Not sure this will work:

def exact_match(query, id, status, metadata) do
      |> where(
           f.id == ^id and f.status == ^status and  f.metadata == ^metadata

So if I invoke exact_match("5d9abc4f-64de-43a5-a95e-d205bc1c291a", "success", %{"a" -> "7890"} then it should just get that record. But it doesn’t and returns a [] an empty list, meaning … none found!

Here’s a print of the query generated:

#Ecto.Query<from f0 in X.Y.Projections.Schema.FH,
 prefix: "sys_xyz",
 where: f0.id == ^"5d9abc4f-64de-43a5-a95e-d205bc1c291a" and f0.status == ^"success" and f0.metadata == ^%{"a" => "7890"}

This record exists, but is not retreived, any ideas/suggestions? I want to match on the content of the map as a whole.

Take a look at this one

The example given explains retrieving based on individual keys, but I want to retrieve it based on the whole map?! Possible? Something like, apart from id and status, the match should also exist on metadata as a whole?