Hello ,

I’m trying to define a one-to-many association where the child’s foreign key is contained inside a jsonb field.
I have a parent table (let’s just call it parent) with an integer primary key field id.
I have a child table called child with ecto schema Child and a field args of type jsonb.
The field args is a json object with the following structrure:

{
  parent_id, <- this points to the primary key of "parent"
  ...
  <other_fields>
}

I would like to define a has_many association on the parent’s schema, something along these lines:

schema "parent" do 

  has_many :children, Child, foreign_key: fragment("(args->>'parent_id')::int")
end

I’m aware that this won’t work in Ecto, but it will hopefully convey what I’m trying to achieve.

I’m also aware that I could change the child’s table’s schema to add an ordinary foreign key, but it’s something I would like to avoid because the table and its schema are managed by an external library. Also, I don’t want to clutter the table with ad-hoc keys for this specific use case, as the same table will be used in other scenarios.

My workaround would have been to manage the children field myself without Ecto. That would of course work.

However, before I go down that path, I wanted to ask if there is a way to achieve this directly with Ecto. I suspect that this is not possible, but perhaps someone here has some suggestions on how to “trick” Ecto into recognizing this kind of association.

Thank you very much in advance!