Writing queries with multiple from clauses

Questions / Help
1

:wave: I’m trying to write a postgres query in ecto that uses multiple from clauses. Like this:

SELECT * FROM table, jsonb_array_elements(table.column) AS other WHERE other->>'id' = 'x';

My aim is to query table by jsonb array elements (embeds_many) and this query works fine in raw SQL, but I can’t figure out how to tell ecto I have multiple from clauses.

1 Like
2

For future travellers (as I found this really hard to search for). The solution to this is that:

FROM x, y

Is semantically the same as a cross join and identical in result to a natural join, so the above can be described in Ecto as:

from(t in Table, cross_join: c in fragment("jsonb_array_elements(?)", t.column), where: fragment("?->>'id' = ?", c, ^c_id))
13 Likes
3

Thanks JonRowe, I face the same requirement and found your solution.
I had change in migration file for field from {:array, :map} to :map so can use jsonb_array_elements function but take a difficult to build query in Ecto with fragment before see your post.
For future travellers: if want to select c, we can use fragment:
from(t in Table, cross_join: c in fragment(“jsonb_array_elements(?)”, t.column), select: fragment("?", c), where: fragment("?->>‘id’ = ?", c, ^c_id))

2 Likes