Repository: elixir-ecto/ecto
Pull request #4193 (2023-05-26 04:11:13)

add array_join
This PR probes for the core team's opinion on adding support for [`ARRAY JOIN`](https://clickhouse.com/docs/en/sql-reference/statements/select/array-join) as used in ClickHouse. If the core team approves of it, I'll add changes to the docs (mentioning that it's ClickHouse-specific) and tests in subsequent commits.

Here's how it could be used in the Ecto adapter for ClickHouse: https://github.com/plausible/ecto_ch/pull/76/files

```elixir
test "array join" do
  query =
    from at in "arrays_test",
      array_join: a in "arr",
      select: [at.s, a]

  assert all(query) == """
    SELECT a0."s",a1 FROM "arrays_test" AS a0 ARRAY JOIN "arr" AS a1\
    """
end
```

---

Right now `:lateral_join` is used in that Ecto adapter to express array joins:

- [connection.ex](https://github.com/plausible/ecto_ch/blob/0e424d64c3c754b4a35df57d80ddd5743416f564/lib/ecto/adapters/clickhouse/connection.ex#L341-L342)
- https://github.com/plausible/ecto_ch#caveats
- [Plausible examples](https://github.com/search?q=repo%3Aplausible%2Fanalytics%20lateral&type=code)

v0idpwn: For me it seems okay, mostly because I don't see good enough alternative paths for the adapter to support other qualifiers.
josevalim: The only other way I see to extend this is to stop validating the first argument of `join`, so you can pass anything, but I am not convinced that's better, so this sounds good to me too!

Sounds good to me too, as I don't see another way to extend this.

Patch:
diff --git a/lib/ecto/query.ex b/lib/ecto/query.ex
index c45896dd..6e959ad7 100644
--- a/lib/ecto/query.ex
+++ b/lib/ecto/query.ex
@@ -795,7 +795,7 @@ defmodule Ecto.Query do
   defp wrap_in_subquery(queryable), do: %Ecto.SubQuery{query: Ecto.Queryable.to_query(queryable)}
 
   @joins [:join, :inner_join, :cross_join, :cross_lateral_join, :left_join, :right_join, :full_join,
-          :inner_lateral_join, :left_lateral_join]
+          :inner_lateral_join, :left_lateral_join, :array_join, :left_array_join]
 
   @doc """
   Puts the given prefix in a query.
@@ -843,6 +843,8 @@ defmodule Ecto.Query do
       Ecto.Query.exclude(query, :full_join)
       Ecto.Query.exclude(query, :inner_lateral_join)
       Ecto.Query.exclude(query, :left_lateral_join)
+      Ecto.Query.exclude(query, :array_join)
+      Ecto.Query.exclude(query, :left_array_join)
 
   However, keep in mind that if a join is removed and its bindings
   were referenced elsewhere, the bindings won't be removed, leading
@@ -1053,6 +1055,8 @@ defmodule Ecto.Query do
   defp join_qual(:cross_lateral_join), do: :cross_lateral
   defp join_qual(:left_lateral_join), do: :left_lateral
   defp join_qual(:inner_lateral_join), do: :inner_lateral
+  defp join_qual(:array_join), do: :array
+  defp join_qual(:left_array_join), do: :left_array
 
   defp collect_with_ties([{:with_ties, with_ties} | t], nil),
     do: collect_with_ties(t, with_ties)
@@ -1094,11 +1098,12 @@ defmodule Ecto.Query do
   Receives a source that is to be joined to the query and a condition for
   the join. The join condition can be any expression that evaluates
   to a boolean value. The qualifier must be one of `:inner`, `:left`,
-  `:right`, `:cross`, `:cross_lateral`, `:full`, `:inner_lateral` or `:left_lateral`.
+  `:right`, `:cross`, `:cross_lateral`, `:full`, `:inner_lateral`, `:left_lateral`,
+  `:array` or `:left_array`.
 
   For a keyword query the `:join` keyword can be changed to `:inner_join`,
-  `:left_join`, `:right_join`, `:cross_join`, `:cross_lateral_join`, `:full_join`, `:inner_lateral_join`
-  or `:left_lateral_join`. `:join` is equivalent to `:inner_join`.
+  `:left_join`, `:right_join`, `:cross_join`, `:cross_lateral_join`, `:full_join`, `:inner_lateral_join`,
+  `:left_lateral_join`, `:array_join` or `:left_array_join`. `:join` is equivalent to `:inner_join`.
 
   Currently it is possible to join on:
 
@@ -1230,6 +1235,17 @@ defmodule Ecto.Query do
       from e in Event,
         hints: [sample: sample_threshold()],
         select: e
+        
+  ## Array joins
+  
+  The `:array` and `:left_array` qualifiers can be used to join with array
+  columns in [Clickhouse:](https://clickhouse.com/docs/en/sql-reference/statements/select/array-join)
+
+      from at in "arrays_test",
+        array_join: a in "arr",
+        select: %{s: at.s, arr: a}
+
+  Note that only the columns in the base table (i.e. the table referenced in `FROM`) can be used in the array join.
 
   """
   @join_opts [:on | @from_join_opts]
diff --git a/lib/ecto/query/builder.ex b/lib/ecto/query/builder.ex
index b615ad6a..73adc906 100644
--- a/lib/ecto/query/builder.ex
+++ b/lib/ecto/query/builder.ex
@@ -1338,4 +1338,9 @@ defmodule Ecto.Query.Builder do
        when is_atom(var) and is_atom(context) and is_atom(field) do
     {expr, acc}
   end
+
+  defp parse_access_get({{:., _, [{kind, _, [_]}, field]}, _, []} = expr, acc)
+       when kind in [:as, :parent_as] and is_atom(field) do
+    {expr, acc}
+  end
 end
diff --git a/lib/ecto/query/builder/join.ex b/lib/ecto/query/builder/join.ex
index 5e8a5072..d98ca5df 100644
--- a/lib/ecto/query/builder/join.ex
+++ b/lib/ecto/query/builder/join.ex
@@ -236,7 +236,7 @@ defmodule Ecto.Query.Builder.Join do
 
   defp ensure_on(on, _assoc, _qual, _source, _env) when on != nil, do: on
 
-  defp ensure_on(nil, _assoc = nil, qual, source, env) when qual not in [:cross, :cross_lateral] do
+  defp ensure_on(nil, _assoc = nil, qual, source, env) when qual not in [:cross, :cross_lateral, :array, :left_array] do
     maybe_source =
       with {source, alias} <- source,
         source when source != nil <- source || alias do
@@ -323,7 +323,7 @@ defmodule Ecto.Query.Builder.Join do
     end
   end
 
-  @qualifiers [:inner, :inner_lateral, :left, :left_lateral, :right, :full, :cross, :cross_lateral]
+  @qualifiers [:inner, :inner_lateral, :left, :left_lateral, :right, :full, :cross, :cross_lateral, :array, :left_array]
 
   @doc """
   Called at runtime to check dynamic qualifier.
diff --git a/lib/ecto/query/inspect.ex b/lib/ecto/query/inspect.ex
index 4d4813e5..fbdf463c 100644
--- a/lib/ecto/query/inspect.ex
+++ b/lib/ecto/query/inspect.ex
@@ -364,6 +364,8 @@ defimpl Inspect, for: Ecto.Query do
   defp join_qual(:full), do: :full_join
   defp join_qual(:cross), do: :cross_join
   defp join_qual(:cross_lateral), do: :cross_lateral_join
+  defp join_qual(:array), do: :array_join
+  defp join_qual(:left_array), do: :left_array_join
 
   defp collect_sources(%{from: nil, joins: joins}) do
     ["query" | join_sources(joins)]
diff --git a/lib/ecto/query/planner.ex b/lib/ecto/query/planner.ex
index b7f5284e..519b8794 100644
--- a/lib/ecto/query/planner.ex
+++ b/lib/ecto/query/planner.ex
@@ -1135,7 +1135,7 @@ defmodule Ecto.Query.Planner do
 
       {:map, _} ->
         :ok
-      
+
       {:parameterized, type, _} ->
         validate_json_path!(path, field, type)
 
@@ -1267,14 +1267,15 @@ defmodule Ecto.Query.Planner do
   end
 
   defp prewalk({:json_extract_path, meta, [json_field, path]}, kind, query, expr, acc, _adapter) do
-    {{:., dot_meta, [{:&, amp_meta, [ix]}, field]}, expr_meta, []} = json_field
+    {{:., dot_meta, [left, field]}, expr_meta, []} = json_field
+    {ix, ix_expr, ix_query} = get_ix!(left, kind, query)
 
-    type = type!(kind, query, expr, ix, field)
+    type = type!(kind, ix_query, expr, ix, field)
     validate_json_path!(path, field, type)
 
-    field_source = kind |> get_source!(query, ix) |> field_source(field)
+    field_source = kind |> get_source!(ix_query, ix) |> field_source(field)
 
-    json_field = {{:., dot_meta, [{:&, amp_meta, [ix]}, field_source]}, expr_meta, []}
+    json_field = {{:., dot_meta, [ix_expr, field_source]}, expr_meta, []}
     {{:json_extract_path, meta, [json_field, path]}, acc}
   end
 


Test patch:
diff --git a/test/ecto/query/builder_test.exs b/test/ecto/query/builder_test.exs
index 52aad030..29187aab 100644
--- a/test/ecto/query/builder_test.exs
+++ b/test/ecto/query/builder_test.exs
@@ -60,6 +60,22 @@ defmodule Ecto.Query.BuilderTest do
     actual = escape(quote do x.y["a"]["b"] end, [x: 0], __ENV__)
     assert actual == expected
 
+    expected = {Macro.escape(quote do: json_extract_path(as(:x).y(), ["a", "b"])), []}
+    actual = escape(quote do json_extract_path(as(:x).y, ["a", "b"]) end, [], __ENV__)
+    assert actual == expected
+
+    expected = {Macro.escape(quote do: json_extract_path(parent_as(:x).y(), ["a", "b"])), []}
+    actual = escape(quote do json_extract_path(parent_as(:x).y, ["a", "b"]) end, [], __ENV__)
+    assert actual == expected
+
+    expected = {Macro.escape(quote do: json_extract_path(as(:x).y(), ["a", "b"])), []}
+    actual = escape(quote do as(:x).y["a"]["b"] end, [], __ENV__)
+    assert actual == expected
+
+    expected = {Macro.escape(quote do: json_extract_path(parent_as(:x).y(), ["a", "b"])), []}
+    actual = escape(quote do parent_as(:x).y["a"]["b"] end, [], __ENV__)
+    assert actual == expected
+
     expected = {Macro.escape(quote do: json_extract_path(&0.y(), ["a", 0])), []}
     actual = escape(quote do x.y["a"][0] end, [x: 0], __ENV__)
     assert actual == expected
diff --git a/test/ecto/query/inspect_test.exs b/test/ecto/query/inspect_test.exs
index fa1797a3..82b21f25 100644
--- a/test/ecto/query/inspect_test.exs
+++ b/test/ecto/query/inspect_test.exs
@@ -137,6 +137,12 @@ defmodule Ecto.Query.InspectTest do
 
     assert i(from(x in Post, cross_lateral_join: y in Comment, on: x.id == y.id)) ==
            ~s{from p0 in Inspect.Post, cross_lateral_join: c1 in Inspect.Comment, on: p0.id == c1.id}
+           
+    assert i(from(x in Post, array_join: y in "arr")) == 
+           ~s{from p0 in Inspect.Post, array_join: a1 in "arr", on: true}
+    
+    assert i(from(x in Post, left_array_join: y in "arr")) == 
+           ~s{from p0 in Inspect.Post, left_array_join: a1 in "arr", on: true}
 
     binding = :comments
     assert i(from(x in Post, left_join: y in assoc(x, ^binding), as: ^binding)) ==
diff --git a/test/ecto/query/planner_test.exs b/test/ecto/query/planner_test.exs
index b5fb3257..139e4145 100644
--- a/test/ecto/query/planner_test.exs
+++ b/test/ecto/query/planner_test.exs
@@ -99,9 +99,9 @@ defmodule Ecto.Query.PlannerTest do
     def cast(data, _) do
       {:ok, data}
     end
-    
+
     def dump(data, _, _), do: {:ok, data}
-    
+
     def load(data, _, _), do: {:ok, data}
   end
 
@@ -1273,6 +1273,48 @@ defmodule Ecto.Query.PlannerTest do
              "where: c0.custom_map[\"field\"] == \"value\", select: [c0.custom_map[\"field\"], c0.custom_embed[\"nested_custom_map\"][\"field\"]]>"
   end
 
+  test "normalize: json_extract_path with as/1" do
+    normalized_query =
+      Comment
+      |> join(:inner, [_], p in Post, as: :post, on: true)
+      |> where([p], as(:post).prefs["field"] == "value")
+      |> normalize()
+
+    assert inspect(normalized_query) =~ "where: p1.preferences[\"field\"] == \"value\""
+
+    normalized_query =
+      Comment
+      |> join(:inner, [_], p in Post, as: :post, on: true)
+      |> where([p], json_extract_path(as(:post).prefs, ["field"]) == "value")
+      |> normalize()
+
+    assert inspect(normalized_query) =~ "where: p1.preferences[\"field\"] == \"value\""
+  end
+
+  test "normalize: json_extract_path with parent_as/1" do
+    subquery =
+      Comment
+      |> where([c], c.post_id == parent_as(:post).prefs["field"])
+
+    normalized_query =
+      from(Post, as: :post)
+      |> join(:inner, [_], s in subquery(subquery), on: true)
+      |> normalize()
+
+    assert inspect(normalized_query) =~ "where: c0.post_id == parent_as(:post).preferences[\"field\"]"
+
+    subquery =
+      Comment
+      |> where([c], c.post_id == json_extract_path(parent_as(:post).prefs, ["field"]))
+
+    normalized_query =
+      from(Post, as: :post)
+      |> join(:inner, [_], s in subquery(subquery), on: true)
+      |> normalize()
+
+    assert inspect(normalized_query) =~ "where: c0.post_id == parent_as(:post).preferences[\"field\"]"
+  end
+
   test "normalize: flattens and expands right side of in expressions" do
     {query, cast_params, dump_params, _select} = where(Post, [p], p.id in [1, 2, 3]) |> normalize_with_params()
     assert Macro.to_string(hd(query.wheres).expr) == "&0.id() in [1, 2, 3]"
diff --git a/test/ecto/query_test.exs b/test/ecto/query_test.exs
index beb0fb20..df90e37d 100644
--- a/test/ecto/query_test.exs
+++ b/test/ecto/query_test.exs
@@ -743,6 +743,8 @@ defmodule Ecto.QueryTest do
       full_query          = from p in "posts", full_join: b in "blogs", on: true
       inner_lateral_query = from p in "posts", inner_lateral_join: b in "blogs", on: true
       left_lateral_query  = from p in "posts", left_lateral_join: b in "blogs", on: true
+      array_query         = from p in "posts", array_join: b in "blogs"
+      left_array_query    = from p in "posts", left_array_join: b in "blogs"
 
       refute inner_query.joins == base.joins
       refute cross_query.joins == base.joins
@@ -752,6 +754,8 @@ defmodule Ecto.QueryTest do
       refute full_query.joins == base.joins
       refute inner_lateral_query.joins == base.joins
       refute left_lateral_query.joins == base.joins
+      refute array_query.joins == base.joins
+      refute left_array_query.joins == base.joins
 
       excluded_inner_query = exclude(inner_query, :inner_join)
       assert excluded_inner_query.joins == base.joins
@@ -776,6 +780,12 @@ defmodule Ecto.QueryTest do
 
       excluded_left_lateral_query = exclude(left_lateral_query, :left_lateral_join)
       assert excluded_left_lateral_query.joins == base.joins
+      
+      excluded_array_query = exclude(array_query, :array_join)
+      assert excluded_array_query.joins == base.joins
+      
+      excluded_left_array_query = exclude(left_array_query, :left_array_join)
+      assert excluded_left_array_query.joins == base.joins
     end
 
     test "removes join qualifiers with named bindings" do
@@ -802,7 +812,11 @@ defmodule Ecto.QueryTest do
           as: :blogs_il,
           left_lateral_join: bll in "blogs",
           on: true,
-          as: :blogs_ll
+          as: :blogs_ll,
+          array_join: ba in "blogs",
+          as: :blogs_a,
+          left_array_join: bla in "blogs",
+          as: :blogs_la
 
       original_joins_number = length(query.joins)
       original_aliases_number = map_size(query.aliases)
@@ -854,6 +868,18 @@ defmodule Ecto.QueryTest do
       assert map_size(excluded_left_lateral_join_query.aliases) == original_aliases_number - 1
       refute Map.has_key?(excluded_left_lateral_join_query.aliases, :blogs_ll)
       assert Map.has_key?(excluded_left_lateral_join_query.aliases, :base)
+      
+      excluded_array_join_query = exclude(query, :array_join)
+      assert length(excluded_array_join_query.joins) == original_joins_number - 1
+      assert map_size(excluded_array_join_query.aliases) == original_aliases_number - 1
+      refute Map.has_key?(excluded_array_join_query.aliases, :blogs_a)
+      assert Map.has_key?(excluded_array_join_query.aliases, :base)
+      
+      excluded_left_array_join_query = exclude(query, :left_array_join)
+      assert length(excluded_left_array_join_query.joins) == original_joins_number - 1
+      assert map_size(excluded_left_array_join_query.aliases) == original_aliases_number - 1
+      refute Map.has_key?(excluded_left_array_join_query.aliases, :blogs_la)
+      assert Map.has_key?(excluded_left_array_join_query.aliases, :base)
 
       excluded_all_joins_query = exclude(query, :join)
       assert excluded_all_joins_query.joins == []