Postgres: JSON operators are parsed as accessors rather than binary operators, so operands bind wrongly

[RogerHYang]

Summary

->, ->>, #>, #>> and ? are entries in COLUMN_OPERATORS, so _parse_column_ops handles them alongside . and ::. That parses them as accessors: they bind at the tightest precedence tier, and their right-hand side is parsed inconsistently — _parse_column_reference() when it looks like a field, otherwise a full _parse_bitwise() expression, which is looser than the operator itself.

In Postgres they are ordinary binary operators in the "any other operator" tier: below *, /, +, -, level with ||, left associative. Their right operand should take everything that binds tighter and stop at the same tier.

Four misparses follow:

	input	SQLGlot builds	Postgres means
1	a #>> b::TEXT[]	CAST(a #>> b AS TEXT[])	a #>> CAST(b AS TEXT[])
2	a -> b[1]	(a -> b)[1]	a -> b[1]
3	a -> b.c -> d	a -> (b.c -> d)	(a -> b.c) -> d
4	a -> b + 1	(a -> b) + 1	a -> (b + 1)

1–3 are the operand not being allowed to own its own suffixes; 4 is the tier. They are the same root cause, but they do not have the same fix: 1–3 can be repaired inside _parse_column_ops, while 4 requires moving the operators to another precedence level. I have a patch for 1–3 (below); it does not fix 4.

Case 4 in particular cannot be fixed by making the operand parser greedier: + has to end up inside the operand while || has to stay outside it, so the boundary is a precedence tier rather than a rule about suffixes (engine results below).

All four, reproducible as-is:

import sqlglot  # main @ 896032a7

cases = [
    ("""SELECT '{"a":4}'::jsonb #>> '{a}'::text[]""", "postgres"),  # 1 cast on the path
    ("SELECT a -> b[1]", "postgres"),                               # 2 subscript on the operand
    ("SELECT a -> b.c -> d", "postgres"),                           # 3 associativity
    ("SELECT '[1,2,3]'::json -> 1 + 1", "duckdb"),                  # 4 precedence vs +
]
for sql, write in cases:
    print(sqlglot.parse_one(sql, read="postgres").sql(dialect=write))

SELECT CAST(CAST('{"a":4}' AS JSONB) #>> '{a}' AS TEXT[])     -- want: ... #>> CAST('{a}' AS TEXT[])
SELECT JSON_EXTRACT_PATH(a, b)[1]                             -- want: JSON_EXTRACT_PATH(a, b[1])
SELECT JSON_EXTRACT_PATH(a, JSON_EXTRACT_PATH(b.c, d))        -- want: JSON_EXTRACT_PATH(JSON_EXTRACT_PATH(a, b.c), d)
SELECT (CAST('[1,2,3]' AS JSON) -> '$[1]') + 1                -- want: grouping json -> (1 + 1), which PG evaluates to 3

Case 4 is written to DuckDB because the Postgres output is unparenthesised and hides the grouping; the DuckDB rendering makes it explicit, and that emitted statement errors in both engines while the input returns 3.

Round-tripping does not reveal any of them. The generator faithfully prints the tree it was given, so parse → generate → parse is idempotent on the wrong tree, and for ->/->> the output is a JSON_EXTRACT_PATH(...) call where the misplacement is easy to miss.

The blocking case

Postgres emits this form itself. Note the index below is created without any ::text[]; the deparser adds it, so anyone reading an index definition out of the catalog gets this spelling whether or not the author wrote it (PostgreSQL 17.10):

CREATE TEMP TABLE spans (attributes jsonb);
CREATE INDEX ix_spans_session_id ON spans (((attributes #>> '{session,id}')::varchar));
SELECT pg_get_indexdef('ix_spans_session_id'::regclass);

CREATE INDEX ix_spans_session_id ON pg_temp.spans USING btree ((((attributes #>> '{session,id}'::text[]))::character varying))

statement	PostgreSQL 17.10
SELECT '{"a":4}'::jsonb #>> '{a}'::text[] (the original)	4
what SQLGlot emits	ERROR: malformed array literal: "4"

The ::text[] describes the path, not the extracted value. We publish index definitions read from the catalog as the spelling callers should use in order to hit the index; the reproduced statement then fails, so this is blocking us.

The precedence case

statement	PostgreSQL 17.10	DuckDB 1.5.5
SELECT '[1,2,3]'::json -> 1 + 1 (the original)	3	3
SELECT ('[1,2,3]'::json -> 1) + 1 — what SQLGlot builds (root node is Add)	ERROR: operator does not exist: json + integer	Binder Error: No function matches '+(JSON, INTEGER_LITERAL)'

Diagnosis

Operators that Postgres places in the same tier, but that SQLGlot parses as ordinary binary operators, show none of the four problems. Grouping by AST shape (the generated SQL is unparenthesised in the last two rows, so the node types are what matter):

parsed (postgres)	root node	right operand	grouping
a || b::TEXT	DPipe	Cast	a || (b::TEXT)
a || b[1]	DPipe	Bracket	a || (b[1])
a || b.c || d	DPipe	Column	(a || b.c) || d
a || b + 1	DPipe	Add	a || (b + 1)
a @> b::TEXT	ArrayContainsAll	Cast	a @> (b::TEXT)

Same four shapes, correct every time, because a binary operator's operand is parsed at the right level and its place in the chain supplies associativity. -> gets neither.

Scope

operator	PostgreSQL 17.10	DuckDB 1.5.5
->	affected	affected
->>	affected	affected
#>	affected	not DuckDB syntax
#>>	affected	not DuckDB syntax
?	affected	not DuckDB syntax
@>	correct today (parsed via RANGE_PARSERS)	—

Supporting engine results (subscripts, associativity, left operand, the +/|| boundary)

Subscript binds to the operand. DuckDB: '{"a":[1,2,3]}'::JSON -> 'a'[1] returns [1,2,3] (subscript applied to 'a'), while ('{"a":[1,2,3]}'::JSON -> 'a')[1] returns 2. SQLGlot builds the second.

Cast binds to the operand. DuckDB: '{"a":4}'::JSON ->> 'a'::INT fails with Could not convert string 'a' to INT32.

Associativity. Postgres groups the operators left to right: SELECT '{"a":{"b":{"c":1}}}'::json -> 'a' -> 'b' returns {"c":1}, while the right-associative reading json -> ('a' -> 'b') errors with operator is not unique: unknown -> unknown. In SQLGlot a -> b -> c groups correctly; only the qualified-operand form flips, via the dotted-name special case in _parse_column_ops: sqlglot.parse_one("SELECT a -> b.c -> d", read="postgres").sql(dialect="postgres") gives JSON_EXTRACT_PATH(a, JSON_EXTRACT_PATH(b.c, d)).

An operator on the left is mis-grouped too (- is defined on jsonb and binds tighter than ->):

statement	PostgreSQL 17.10
SELECT '{"a":{"x":1},"b":2}'::jsonb - 'b' -> 'a' (the original)	{"x": 1}
SELECT '{"a":{"x":1},"b":2}'::jsonb - ('b' -> 'a') — what SQLGlot builds	ERROR: operator is not unique: unknown -> unknown

|| before the arrow. SELECT 'x' || '{"a":4}'::json ->> 'a' fails in Postgres with operator does not exist: text ->> unknown — the error shows || ran first. SQLGlot parses it as 'x' || (json ->> 'a') and gives it the meaning x4. DuckDB groups it like Postgres.

The operand boundary is a tier, not a rule about suffixes. + has to end up inside the operand and || has to stay outside it:

statement	PostgreSQL 17.10
SELECT '{"a":"4"}'::json ->> 'a' || 'z'	4z
SELECT '{"a":"4"}'::json ->> ('a' || 'z')	NULL

The current operand parse already crosses that line when it falls back to _parse_bitwise(), which is looser than ||:

PG:      SELECT '[1,2,3]'::json ->> -1 || 'z'   =>  3z
sqlglot: JSON_EXTRACT_PATH_TEXT(CAST('[1,2,3]' AS JSON), -1 || 'z')

So x ->> 'a' || 'z' parses correctly today while x ->> -1 || 'z' does not.

Suggested direction

The same shape as #7046, which moved Snowflake's : variant extract so that it runs after _parse_column_ops instead of inside it: take these five out of COLUMN_OPERATORS and parse them in the _parse_bitwise loop, which is already a left-associative loop over _parse_term containing DPipe. Postgres puts ->, || and the bitwise operators in one tier below +/-, so that single move should give the engine's grouping for all four cases.

Two things make it more than a mechanical move:

• -> is also lambda syntax in DuckDB, Snowflake, ClickHouse and Spark, so it likely needs dialect gating rather than a change in the base parser.
• COLUMN_OPERATORS mixes two kinds of operator: these five, whose right side is an expression, and genuine accessors whose right side is a field name — SingleStore's ::, ClickHouse's .^, Snowflake's !. A rule applied to the whole mapping breaks the second group: SingleStore's a::b :> INT must stay JSON_EXTRACT_JSON(a, 'b') :> INT, and pulling the cast onto b silently drops it, because the path builder reads path.name.

I am happy to do the work either way — this is blocking us, so I would rather carry it than wait on it.

For cases 1–3 I already have a tested patch: the operand keeps its own ., [...] and ::, gated on an explicit set of operators so the accessors above are untouched. Full suite green pure-Python and under mypyc, and a tree diff over ~900 parses across 15 dialects shows only the intended cases changing. I can open that as a PR immediately, with case 4 left open.

I am also willing to attempt the full move into _parse_bitwise. The part I would want your steer on first is the gating: should the arrows move only for Postgres/DuckDB, or move in the base parser with the lambda dialects opting out? Either is fine by me, but it determines how the change is structured.

References

• PostgreSQL operator precedence: https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-PRECEDENCE
• PostgreSQL JSON operators: https://www.postgresql.org/docs/current/functions-json.html
• DuckDB JSON functions and operators: https://duckdb.org/docs/stable/data/json/json_functions
• Prior art: Fix(snowflake)!: refactor colon (extract) parsing precedence #7046 (Snowflake colon extract precedence), fix(duckdb): Fix NOT precedence for JSON extractions #6670 (DuckDB NOT precedence for JSON extractions), Postgres' inline power operator has incorrect operator precedence #1555 (Postgres ^ precedence)

Environment

• sqlglot main @ 896032a7
• PostgreSQL 17.10 (aarch64-unknown-linux-musl, Alpine)
• DuckDB 1.5.5
• Python 3.13

---

Investigated with LLM assistance; every engine result above was run against live PostgreSQL 17.10 and DuckDB 1.5.5.

[fivetran-kwoodbeck]

Thanks for the detailed report @RogerHYang ! Confirmed on main, all four cases reproduce and the diagnosis matches the code.

Given that you've got a patch for cases 1-3, feel free to open a PR for review.

One thing to watch for on case 1: it appears that RHS does not reach _parse_bitwise; _parse_column_reference() → _parse_field() captures bare literals too, so the ::text[] is left for the outer column-op loop.