Ecto Query: Select results "into" a schema

Questions / Help
1

Background

To make a long story short: I have a rather complex query joining over multiple tables and aggregating various fields into a JSON array column using Postgres’ json_agg. This query used to live in a view, I had a schema pointing to that view using embeds_many for the JSON column; all was fine and dandy.

Then BAM, in came a requirement which made it necessary to slightly modify aforementioned query for each request. Speak having this query in a view was no longer a viable option.

Problem

I’ve migrated this query to the ecto equivalent using a fragment for the JSON column aggregation. Here is a simplified version of the query in question to give you an impression what I’m dealing with:

from t1 in "table1",
  join: t2 in "table2",
  on: t1.t2_id == t2.id,
  group_by: t2.id,
  select: struct(m, @fields),
  select_merge: %MySchema{
    id: t2.id,
    # Some more "simple" columns ...
    my_json_column:
      fragment(
        """
        json_agg(
          json_build_object(
            'id', ?,
            'some_field', ?,
            'another_field', ?
          )
        )
        """,
        t1.id,
        t1.some_field,
        t1.another_field
      )
  }

While this works fine, I’m struggling to decode the JSON column as embedded schemas. All attempts at telling ecto to decode this column as a schema failed:

1. using type/2 to case the fragment to the schema

type(fragment("json_agg(...)"), MyEmbeddedSchema)

I also tried to use the return value of MySchema.__schema__(:type, :my_json_column) - which is {:embed, %Ecto.Embedded{...}} - but this failed too. I even tried to use m.my_json_column as type information (as suggested in the docs) but that fails too.

2. use MySchema in the join followed by a select_merge 

join: t2 in {"table2", MySchema},
# ...
select: struct(t2, @fields_without_json_column),
select_merge: %{my_json_column: fragment("...")}

While this works great for all other fields and respects their type information, it again falls flat for the JSON column and just spits out a map with string keys.

Wishful Thinking

Ideally I would like to be able to do something like this:

from t1 in "table1",
  # ...
  select: %{...},
  as: MySchema

Which then takes MySchema and magically decodes the map into MySchema using all the type information available.

2

https://hexdocs.pm/ecto/3.1.7/Ecto.Repo.html#c:load/2

1 Like
3

That is helpful but is there an option to specify this directly on the query?

4

You can use a struct in select: like you would a map:

...
alias MyModule.MyStruct
...
from t1 in "table1",
  # ...
  select: %MyStruct{
    field_1: t1.field2,
    ...
    field_N: t1.fieldN
}

I’ve done this before with joins etc., where I’ve needed a struct for use in a protocol where a map wouldn’t do. You could probably do it with an Ecto.Schema defined just for this purpose as well. I’ve never tried it, but I’m guessing it would work if you need an actual schema. After all, a schema is basically just a struct.

1 Like
5

When you look at the query from above: I did initially exactly that but the JSON column doesn’t get decoded into the embedded schema when doing that.