From d37d56c1ad04a8fb99ff8ed70973197907abf631 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Fri, 1 Aug 2025 12:48:15 -0400 Subject: [PATCH] Fix PostgreSQL JSONB subscripting regression with functions Fixed regression in PostgreSQL dialect where JSONB subscription syntax would generate incorrect SQL for JSONB-returning functions, causing syntax errors. The dialect now properly wraps function calls and expressions in parentheses when using the ``[]`` subscription syntax, generating ``(function_call)[index]`` instead of ``function_call[index]`` to comply with PostgreSQL syntax requirements. Fixes: #12778 Change-Id: If1238457e6bba6a933023b26519a41aa5de4dbcd --- doc/build/changelog/unreleased_20/12778.rst | 10 ++++++++ doc/build/tutorial/data_select.rst | 2 +- lib/sqlalchemy/sql/functions.py | 4 +--- test/dialect/postgresql/test_compiler.py | 26 +++++++++++++++++++++ 4 files changed, 38 insertions(+), 4 deletions(-) create mode 100644 doc/build/changelog/unreleased_20/12778.rst diff --git a/doc/build/changelog/unreleased_20/12778.rst b/doc/build/changelog/unreleased_20/12778.rst new file mode 100644 index 0000000000..fc22fc6aa8 --- /dev/null +++ b/doc/build/changelog/unreleased_20/12778.rst @@ -0,0 +1,10 @@ +.. change:: + :tags: bug, postgresql + :tickets: 12778 + + Fixed regression in PostgreSQL dialect where JSONB subscription syntax + would generate incorrect SQL for JSONB-returning functions, causing syntax + errors. The dialect now properly wraps function calls and expressions in + parentheses when using the ``[]`` subscription syntax, generating + ``(function_call)[index]`` instead of ``function_call[index]`` to comply + with PostgreSQL syntax requirements. diff --git a/doc/build/tutorial/data_select.rst b/doc/build/tutorial/data_select.rst index 38faddb61a..d880b4a4ae 100644 --- a/doc/build/tutorial/data_select.rst +++ b/doc/build/tutorial/data_select.rst @@ -1476,7 +1476,7 @@ elements:: >>> stmt = select(function_expr["def"]) >>> print(stmt) - {printsql}SELECT json_object(:json_object_1)[:json_object_2] AS anon_1 + {printsql}SELECT (json_object(:json_object_1))[:json_object_2] AS anon_1 Built-in Functions Have Pre-Configured Return Types ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ diff --git a/lib/sqlalchemy/sql/functions.py b/lib/sqlalchemy/sql/functions.py index 375cb26f13..9a28dcfb4f 100644 --- a/lib/sqlalchemy/sql/functions.py +++ b/lib/sqlalchemy/sql/functions.py @@ -743,9 +743,7 @@ class FunctionElement(Executable, ColumnElement[_T], FromClause, Generative): # expressions against getitem. This may need to be made # more portable if in the future we support other DBs # besides postgresql. - if against is operators.getitem and isinstance( - self.type, sqltypes.ARRAY - ): + if against in (operators.getitem, operators.json_getitem_op): return Grouping(self) else: return super().self_group(against=against) diff --git a/test/dialect/postgresql/test_compiler.py b/test/dialect/postgresql/test_compiler.py index 6c8289867e..5be149cf6a 100644 --- a/test/dialect/postgresql/test_compiler.py +++ b/test/dialect/postgresql/test_compiler.py @@ -2756,6 +2756,32 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): "UPDATE data SET x -> %(x_1)s=(data.x -> %(x_2)s)", ) + def test_jsonb_functions_use_parentheses_with_subscripting(self): + """test #12778 - JSONB functions are parenthesized with [] syntax""" + data = table("data", column("id", Integer), column("x", JSONB)) + + # Test that JSONB functions are properly parenthesized with [] syntax + # This ensures correct PostgreSQL syntax: (function_call)[index] + # instead of the invalid: function_call[index] + + stmt = select(func.jsonb_array_elements(data.c.x, type_=JSONB)["key"]) + self.assert_compile( + stmt, + "SELECT " + "(jsonb_array_elements(data.x))[%(jsonb_array_elements_1)s] " + "AS anon_1 FROM data", + ) + + # Test with nested function calls + stmt = select( + func.jsonb_array_elements(data.c.x["items"], type_=JSONB)["key"] + ) + self.assert_compile( + stmt, + "SELECT (jsonb_array_elements(data.x[%(x_1)s]))" + "[%(jsonb_array_elements_1)s] AS anon_1 FROM data", + ) + def test_range_custom_object_hook(self): # See issue #8884 from datetime import date -- 2.47.2