Repository: tobymao/sqlglot

Issue:
Bug with JSONB_EXIST function translation from Postgresql to DuckDB

**Fully reproducible code snippet**
```python
from sqlglot import parse_one
query = "SELECT JSONB_EXISTS(col, path) from table"
parse_one(query, dialect='postgres').sql(dialect='duckdb')
``` 
the output is: 'SELECT JSONB_EXISTS(col, path) FROM "table"'
while the expected output is: 'SELECT JSON_EXISTS(col, path) FROM "table"'

**Official Documentation**
1. DuckDB json docs: https://duckdb.org/docs/data/json/json_functions
2. Postgres Docs: https://www.postgresql.org/docs/current/functions-json.html. JSONB_EXISTS is mapped to the question mark operator

Thanks!

Discussion:

Patch:
diff --git a/sqlglot/dialects/duckdb.py b/sqlglot/dialects/duckdb.py
index 4a2460f5..391d4603 100644
--- a/sqlglot/dialects/duckdb.py
+++ b/sqlglot/dialects/duckdb.py
@@ -536,6 +536,7 @@ class DuckDB(Dialect):
             exp.IntDiv: lambda self, e: self.binary(e, "//"),
             exp.IsInf: rename_func("ISINF"),
             exp.IsNan: rename_func("ISNAN"),
+            exp.JSONBExists: rename_func("JSON_EXISTS"),
             exp.JSONExtract: _arrow_json_extract_sql,
             exp.JSONExtractScalar: _arrow_json_extract_sql,
             exp.JSONFormat: _json_format_sql,
diff --git a/sqlglot/dialects/postgres.py b/sqlglot/dialects/postgres.py
index 7c431c78..a94bccbd 100644
--- a/sqlglot/dialects/postgres.py
+++ b/sqlglot/dialects/postgres.py
@@ -370,6 +370,7 @@ class Postgres(Dialect):
         FUNCTION_PARSERS = {
             **parser.Parser.FUNCTION_PARSERS,
             "DATE_PART": lambda self: self._parse_date_part(),
+            "JSONB_EXISTS": lambda self: self._parse_jsonb_exists(),
         }
 
         BITWISE = {
@@ -443,6 +444,14 @@ class Postgres(Dialect):
         def _parse_unique_key(self) -> t.Optional[exp.Expression]:
             return None
 
+        def _parse_jsonb_exists(self) -> exp.JSONBExists:
+            return self.expression(
+                exp.JSONBExists,
+                this=self._parse_bitwise(),
+                path=self._match(TokenType.COMMA)
+                and self.dialect.to_json_path(self._parse_bitwise()),
+            )
+
     class Generator(generator.Generator):
         SINGLE_STRING_INTERVAL = True
         RENAME_TABLE_WITH_DB = False
diff --git a/sqlglot/diff.py b/sqlglot/diff.py
index b637c518..ec1e31e6 100644
--- a/sqlglot/diff.py
+++ b/sqlglot/diff.py
@@ -10,6 +10,7 @@ import typing as t
 from collections import defaultdict
 from dataclasses import dataclass
 from heapq import heappop, heappush
+from itertools import chain
 
 from sqlglot import Dialect, expressions as exp
 from sqlglot.helper import seq_get
@@ -112,11 +113,19 @@ def diff(
     def compute_node_mappings(
         original: exp.Expression, copy: exp.Expression
     ) -> t.Dict[int, exp.Expression]:
-        return {
-            id(old_node): new_node
-            for old_node, new_node in zip(original.walk(), copy.walk())
-            if id(old_node) in matching_ids
-        }
+        node_mapping = {}
+        for old_node, new_node in zip(
+            reversed(tuple(original.walk())), reversed(tuple(copy.walk()))
+        ):
+            # We cache the hash of each new node here to speed up equality comparisons. If the input
+            # trees aren't copied, these hashes will be evicted before returning the edit script.
+            new_node._hash = hash(new_node)
+
+            old_node_id = id(old_node)
+            if old_node_id in matching_ids:
+                node_mapping[old_node_id] = new_node
+
+        return node_mapping
 
     source_copy = source.copy() if copy else source
     target_copy = target.copy() if copy else target
@@ -127,13 +136,19 @@ def diff(
     }
     matchings_copy = [(node_mappings[id(s)], node_mappings[id(t)]) for s, t in matchings]
 
-    return ChangeDistiller(**kwargs).diff(
+    edit_script = ChangeDistiller(**kwargs).diff(
         source_copy,
         target_copy,
         matchings=matchings_copy,
         delta_only=delta_only,
     )
 
+    if not copy:
+        for node in chain(source.walk(), target.walk()):
+            node._hash = None
+
+    return edit_script
+
 
 # The expression types for which Update edits are allowed.
 UPDATABLE_EXPRESSION_TYPES = (
@@ -199,11 +214,27 @@ class ChangeDistiller:
             source_node = self._source_index[kept_source_node_id]
             target_node = self._target_index[kept_target_node_id]
 
-            if (
-                not isinstance(source_node, UPDATABLE_EXPRESSION_TYPES)
-                or source_node == target_node
-            ):
-                edit_script.extend(self._generate_move_edits(source_node, target_node, matchings))
+            identical_nodes = source_node == target_node
+
+            if not isinstance(source_node, UPDATABLE_EXPRESSION_TYPES) or identical_nodes:
+                if identical_nodes:
+                    source_parent = source_node.parent
+                    target_parent = target_node.parent
+
+                    if (
+                        (source_parent and not target_parent)
+                        or (not source_parent and target_parent)
+                        or (
+                            source_parent
+                            and target_parent
+                            and matchings.get(id(source_parent)) != id(target_parent)
+                        )
+                    ):
+                        edit_script.append(Move(source=source_node, target=target_node))
+                else:
+                    edit_script.extend(
+                        self._generate_move_edits(source_node, target_node, matchings)
+                    )
 
                 source_non_expression_leaves = dict(_get_non_expression_leaves(source_node))
                 target_non_expression_leaves = dict(_get_non_expression_leaves(target_node))
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index d1adce36..5facee36 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -5960,6 +5960,11 @@ class JSONBContains(Binary, Func):
     _sql_names = ["JSONB_CONTAINS"]
 
 
+class JSONBExists(Func):
+    arg_types = {"this": True, "path": True}
+    _sql_names = ["JSONB_EXISTS"]
+
+
 class JSONExtract(Binary, Func):
     arg_types = {
         "this": True,
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index f528bc9e..6e189958 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -4362,20 +4362,25 @@ class Generator(metaclass=_Generator):
     def arrayagg_sql(self, expression: exp.ArrayAgg) -> str:
         array_agg = self.function_fallback_sql(expression)
 
+        # Add a NULL FILTER on the column to mimic the results going from a dialect that excludes nulls
+        # on ARRAY_AGG (e.g Spark) to one that doesn't (e.g. DuckDB)
         if self.dialect.ARRAY_AGG_INCLUDES_NULLS and expression.args.get("nulls_excluded"):
             parent = expression.parent
             if isinstance(parent, exp.Filter):
                 parent_cond = parent.expression.this
                 parent_cond.replace(parent_cond.and_(expression.this.is_(exp.null()).not_()))
             else:
-                # DISTINCT is already present in the agg function, do not propagate it to FILTER as well
                 this = expression.this
-                this_sql = (
-                    self.expressions(this)
-                    if isinstance(this, exp.Distinct)
-                    else self.sql(expression, "this")
-                )
-                array_agg = f"{array_agg} FILTER(WHERE {this_sql} IS NOT NULL)"
+                # Do not add the filter if the input is not a column (e.g. literal, struct etc)
+                if this.find(exp.Column):
+                    # DISTINCT is already present in the agg function, do not propagate it to FILTER as well
+                    this_sql = (
+                        self.expressions(this)
+                        if isinstance(this, exp.Distinct)
+                        else self.sql(expression, "this")
+                    )
+
+                    array_agg = f"{array_agg} FILTER(WHERE {this_sql} IS NOT NULL)"
 
         return array_agg
 


Test patch:
diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py
index 62ae2472..0f9ab3c2 100644
--- a/tests/dialects/test_postgres.py
+++ b/tests/dialects/test_postgres.py
@@ -797,6 +797,14 @@ class TestPostgres(Validator):
         self.validate_identity("SELECT OVERLAY(a PLACING b FROM 1 FOR 1)")
         self.validate_identity("ARRAY[1, 2, 3] && ARRAY[1, 2]").assert_is(exp.ArrayOverlaps)
 
+        self.validate_all(
+            """SELECT JSONB_EXISTS('{"a": [1,2,3]}', 'a')""",
+            write={
+                "postgres": """SELECT JSONB_EXISTS('{"a": [1,2,3]}', 'a')""",
+                "duckdb": """SELECT JSON_EXISTS('{"a": [1,2,3]}', '$.a')""",
+            },
+        )
+
     def test_ddl(self):
         # Checks that user-defined types are parsed into DataType instead of Identifier
         self.parse_one("CREATE TABLE t (a udt)").this.expressions[0].args["kind"].assert_is(
diff --git a/tests/dialects/test_spark.py b/tests/dialects/test_spark.py
index b0c12b15..486bf792 100644
--- a/tests/dialects/test_spark.py
+++ b/tests/dialects/test_spark.py
@@ -314,6 +314,20 @@ TBLPROPERTIES (
                 "spark": "SELECT COLLECT_LIST(x) FILTER(WHERE x = 5) FROM (SELECT 1 UNION ALL SELECT NULL) AS t(x)",
             },
         )
+        self.validate_all(
+            "SELECT ARRAY_AGG(1)",
+            write={
+                "duckdb": "SELECT ARRAY_AGG(1)",
+                "spark": "SELECT COLLECT_LIST(1)",
+            },
+        )
+        self.validate_all(
+            "SELECT ARRAY_AGG(DISTINCT STRUCT('a'))",
+            write={
+                "duckdb": "SELECT ARRAY_AGG(DISTINCT {'col1': 'a'})",
+                "spark": "SELECT COLLECT_LIST(DISTINCT STRUCT('a' AS col1))",
+            },
+        )
         self.validate_all(
             "SELECT DATE_FORMAT(DATE '2020-01-01', 'EEEE') AS weekday",
             write={
diff --git a/tests/test_diff.py b/tests/test_diff.py
index 65802277..440502ea 100644
--- a/tests/test_diff.py
+++ b/tests/test_diff.py
@@ -2,7 +2,6 @@ import unittest
 
 from sqlglot import exp, parse_one
 from sqlglot.diff import Insert, Move, Remove, Update, diff
-from sqlglot.expressions import Join, to_table
 
 
 def diff_delta_only(source, target, matchings=None, **kwargs):
@@ -14,22 +13,24 @@ class TestDiff(unittest.TestCase):
         self._validate_delta_only(
             diff_delta_only(parse_one("SELECT a + b"), parse_one("SELECT a - b")),
             [
-                Remove(parse_one("a + b")),  # the Add node
-                Insert(parse_one("a - b")),  # the Sub node
+                Remove(expression=parse_one("a + b")),  # the Add node
+                Insert(expression=parse_one("a - b")),  # the Sub node
+                Move(source=parse_one("a"), target=parse_one("a")),  # the `a` Column node
+                Move(source=parse_one("b"), target=parse_one("b")),  # the `b` Column node
             ],
         )
 
         self._validate_delta_only(
             diff_delta_only(parse_one("SELECT a, b, c"), parse_one("SELECT a, c")),
             [
-                Remove(parse_one("b")),  # the Column node
+                Remove(expression=parse_one("b")),  # the Column node
             ],
         )
 
         self._validate_delta_only(
             diff_delta_only(parse_one("SELECT a, b"), parse_one("SELECT a, b, c")),
             [
-                Insert(parse_one("c")),  # the Column node
+                Insert(expression=parse_one("c")),  # the Column node
             ],
         )
 
@@ -40,8 +41,8 @@ class TestDiff(unittest.TestCase):
             ),
             [
                 Update(
-                    to_table("table_one", quoted=False),
-                    to_table("table_two", quoted=False),
+                    source=exp.to_table("table_one", quoted=False),
+                    target=exp.to_table("table_two", quoted=False),
                 ),  # the Table node
             ],
         )
@@ -53,8 +54,12 @@ class TestDiff(unittest.TestCase):
             ),
             [
                 Update(
-                    exp.Lambda(this=exp.to_identifier("a"), expressions=[exp.to_identifier("a")]),
-                    exp.Lambda(this=exp.to_identifier("b"), expressions=[exp.to_identifier("b")]),
+                    source=exp.Lambda(
+                        this=exp.to_identifier("a"), expressions=[exp.to_identifier("a")]
+                    ),
+                    target=exp.Lambda(
+                        this=exp.to_identifier("b"), expressions=[exp.to_identifier("b")]
+                    ),
                 ),
             ],
         )
@@ -65,8 +70,8 @@ class TestDiff(unittest.TestCase):
                 parse_one('SELECT a, b, "my.udf1"()'), parse_one('SELECT a, b, "my.udf2"()')
             ),
             [
-                Insert(parse_one('"my.udf2"()')),
-                Remove(parse_one('"my.udf1"()')),
+                Insert(expression=parse_one('"my.udf2"()')),
+                Remove(expression=parse_one('"my.udf1"()')),
             ],
         )
         self._validate_delta_only(
@@ -75,8 +80,8 @@ class TestDiff(unittest.TestCase):
                 parse_one('SELECT a, b, "my.udf"(x, y, w)'),
             ),
             [
-                Insert(exp.column("w")),
-                Remove(exp.column("z")),
+                Insert(expression=exp.column("w")),
+                Remove(expression=exp.column("z")),
             ],
         )
 
@@ -132,6 +137,19 @@ class TestDiff(unittest.TestCase):
             ],
         )
 
+        expr_src = parse_one("SELECT a as a, b as b FROM t WHERE CONCAT('a', 'b') = 'ab'")
+        expr_tgt = parse_one("SELECT a as a FROM t WHERE CONCAT('a', 'b', b) = 'ab'")
+
+        b_alias = expr_src.selects[1]
+
+        self._validate_delta_only(
+            diff_delta_only(expr_src, expr_tgt),
+            [
+                Remove(expression=b_alias),
+                Move(source=b_alias.this, target=expr_tgt.find(exp.Concat).expressions[-1]),
+            ],
+        )
+
     def test_cte(self):
         expr_src = """
             WITH
@@ -149,23 +167,30 @@ class TestDiff(unittest.TestCase):
         self._validate_delta_only(
             diff_delta_only(parse_one(expr_src), parse_one(expr_tgt)),
             [
-                Remove(parse_one("LOWER(c) AS c")),  # the Alias node
-                Remove(parse_one("LOWER(c)")),  # the Lower node
-                Remove(parse_one("'filter'")),  # the Literal node
-                Insert(parse_one("'different_filter'")),  # the Literal node
+                Remove(expression=parse_one("LOWER(c) AS c")),  # the Alias node
+                Remove(expression=parse_one("LOWER(c)")),  # the Lower node
+                Remove(expression=parse_one("'filter'")),  # the Literal node
+                Insert(expression=parse_one("'different_filter'")),  # the Literal node
+                Move(source=parse_one("c"), target=parse_one("c")),  # the new Column c
             ],
         )
 
     def test_join(self):
-        expr_src = "SELECT a, b FROM t1 LEFT JOIN t2 ON t1.key = t2.key"
-        expr_tgt = "SELECT a, b FROM t1 RIGHT JOIN t2 ON t1.key = t2.key"
+        expr_src = parse_one("SELECT a, b FROM t1 LEFT JOIN t2 ON t1.key = t2.key")
+        expr_tgt = parse_one("SELECT a, b FROM t1 RIGHT JOIN t2 ON t1.key = t2.key")
 
-        changes = diff_delta_only(parse_one(expr_src), parse_one(expr_tgt))
+        src_join = expr_src.find(exp.Join)
+        tgt_join = expr_tgt.find(exp.Join)
 
-        self.assertEqual(len(changes), 2)
-        self.assertTrue(isinstance(changes[0], Remove))
-        self.assertTrue(isinstance(changes[1], Insert))
-        self.assertTrue(all(isinstance(c.expression, Join) for c in changes))
+        self._validate_delta_only(
+            diff_delta_only(expr_src, expr_tgt),
+            [
+                Remove(expression=src_join),
+                Insert(expression=tgt_join),
+                Move(source=exp.to_table("t2"), target=exp.to_table("t2")),
+                Move(source=src_join.args["on"], target=tgt_join.args["on"]),
+            ],
+        )
 
     def test_window_functions(self):
         expr_src = parse_one("SELECT ROW_NUMBER() OVER (PARTITION BY a ORDER BY b)")
@@ -176,8 +201,8 @@ class TestDiff(unittest.TestCase):
         self._validate_delta_only(
             diff_delta_only(expr_src, expr_tgt),
             [
-                Remove(parse_one("ROW_NUMBER()")),
-                Insert(parse_one("RANK()")),
+                Remove(expression=parse_one("ROW_NUMBER()")),
+                Insert(expression=parse_one("RANK()")),
                 Update(source=expr_src.selects[0], target=expr_tgt.selects[0]),
             ],
         )
@@ -197,20 +222,21 @@ class TestDiff(unittest.TestCase):
         self._validate_delta_only(
             diff_delta_only(expr_src, expr_tgt),
             [
-                Remove(expr_src),
-                Insert(expr_tgt),
-                Insert(exp.Literal.number(2)),
-                Insert(exp.Literal.number(3)),
-                Insert(exp.Literal.number(4)),
+                Remove(expression=expr_src),
+                Insert(expression=expr_tgt),
+                Insert(expression=exp.Literal.number(2)),
+                Insert(expression=exp.Literal.number(3)),
+                Insert(expression=exp.Literal.number(4)),
+                Move(source=exp.Literal.number(1), target=exp.Literal.number(1)),
             ],
         )
 
         self._validate_delta_only(
             diff_delta_only(expr_src, expr_tgt, matchings=[(expr_src, expr_tgt)]),
             [
-                Insert(exp.Literal.number(2)),
-                Insert(exp.Literal.number(3)),
-                Insert(exp.Literal.number(4)),
+                Insert(expression=exp.Literal.number(2)),
+                Insert(expression=exp.Literal.number(3)),
+                Insert(expression=exp.Literal.number(4)),
             ],
         )