Discussion: Selecting parts of embeds with `Ecto.Query.API.struct/2`

[greg-rychlewski]

Elixir version

all

Database and Version

all

Ecto Versions

3.12

Database Adapter and Versions (postgrex, myxql, etc)

all

Current behavior

Currently you can use struct/2 to retrieve only certain fields from the underlying table and still have the result return as the struct you want and still have preloads.

My coworker had a situation where they also wanted to restrict the attributes coming from an embedded field within the table. The best I was able to suggest was to use select_merge, like this:

from t in Table, 
  select: struct([t], [:field1, :field2, ...]), 
  select_merge: %{embed_field: json_extract_path(t.embed_field, ["some", "path"]))

There may be a more standard way to do this that I am not thinking of. But assuming the way above is the best way to do it currently, would it make sense to allow something like this

from t in Table, 
  select: struct([t], [:field1, :field2, ..., embed_field: [:sub_field1, :sub_field2, sub_field3: [...]])

Expected behavior

TBD

[josevalim]

Yes, I think allowing that extension on map/struct can be nice.

[greg-rychlewski]

One thing I realized is it would probably be helpful to have a way to extract subsets of json for this behaviour. I think it can get a bit tricky to select each path individually and then try to rebuild the potentially nested object at the end.

The way this is done in Postgres/MySQL is to use functions like this:

json(b)_build_object(key1, value1, key2, value2, ...) (postgres)
json_object(key1, value1, key2, value2, ...) mysql)

What would you think if we introduced Ecto.Query.API.json_object/3? It would take the following arguments:

1. the json(b) field
2. a path list with potentially nested lists, like ["field1", "field2", "field3" => [...]]
3. the type (:jsonb or :json) because postgres can't use the same syntax for both

[greg-rychlewski]

Or I guess the API I suggested makes more sense with the name json_subset. And if we want to add json_object we keep the alternating key/value arguments.