Optional fields in Ecto - How to define it

Questions / Help
1

Hi,

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?

Thanks!

2

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
3

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?

Example:
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
   query
      |> where(
       [f],
           f.id == ^id and f.status == ^status and  f.metadata == ^metadata
      )
end

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.

4

Take a look at this one
https://hexdocs.pm/ecto/3.5.8/Ecto.Query.API.html#json_extract_path/2

5

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?