=?UTF-8?Q?Micka=C3=ABl_Le_Baillif?= <mickael.le.baillif@gmail.com> writes:
> Let's take a look at this example :
> with data as (
> select '{
> "nested1": {"lvl1_k1": "v1"},
> "nested2": {"lvl2_k1":234, "lvl2_k2": "test"}
> }'::jsonb as extra_values,
> '{"aaa": 12}'::jsonb as j1,
> '{"bbb": "azerty", "ccc": "qwerty"}'::jsonb as j2
> )
> select COALESCE(extra_values->'nested1', '{}')
> || COALESCE(extra_values->'nested2', '{}') as correct,
> extra_values->'nested1' || extra_values->'nested2' as bad,
> j1 || j2 as correct2
> from data
> ;
> I'm expecting to get the same result in columns 'correct' and 'bad'
The problem is revealed by EXPLAIN VERBOSE:
CTE Scan on data (cost=0.01..0.04 rows=1 width=32)
Output: (((extra_values -> 'nested1'::text) || extra_values) -> 'nested2'::te
xt)
...
Since the Postgres parser doesn't have any special knowledge about
the meaning of the -> and || operators, it gives them the same precedence,
causing what you wrote to be parsed as
((extra_values->'nested1') || extra_values)->'nested2'
giving the result you show. The COALESCEs aren't having any run-time
impact, they just act like parentheses.
> I can recover to my expected behaviour by forcing a cast to jsonb on the
> second operand :
> SELECT extra_values->'nested1' || (extra_values->'nested2')::jsonb
Again, it's the parentheses not the cast that are fixing it.
regards, tom lane