Using column references in `json_extract_path` doesn't work

[KirillKayumov]

Elixir version

1.16.2

Database and Version

PostgreSQL 14.9

Ecto Versions

ecto: 3.11.2, ecto_sql: 3.11.3

Database Adapter and Versions (postgrex, myxql, etc)

postgrex: 0.18.0

Current behavior

json_extract_path or [] variant doesn't support referencing columns from other tables. The Ecto query below fails to compile:

create table(:test_posts) do
  add(:text, :text)
  add(:user_id, references(:users))
end

create table(:test_users) do
  add(:post_stats, :jsonb)
end

from(u in TestUser, join: p in assoc(u, :posts), select: %{stat: u.post_stats[p.id]}) |> Repo.all

Expected behavior

It would be very convenient if we could pass references to other columns in json_extract_path. I'm not sure about other DBs but for PostgreSQL I can see that json_extract_path transforms to this SQL:

users.post_stats #> '{"my", "path", "inside", "json"}'

Because it uses such syntax of array (as a '{}' string) it's clear why it doesn't work today. However, PostgreSQL supports another syntax where it's possible to reference columns:

users.post_stats #> array['my', 'path', test_posts.id, 'inside', 'json']::text[]

I'm wondering if anything stops us from using the array[] syntax and allow referencing columns from other tables? If no, I can try to work on the fix.