Postgres Pro
Downloads
Home   >   mailing lists

Thread: [GENERAL] JSON objects merge using || operator

[GENERAL] JSON objects merge using || operator

From
Mickaël Le Baillif
Date:
Hello,

I've encountered a disturbing behaviour using the || operator on two jsonb objects extracted from subfields of a common jsonb object.

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', which is :
{"lvl1_k1": "v1", "lvl2_k1": 234, "lvl2_k2": "test"}

But what I'm getting in column 'bad' is only the right operand :
{"lvl2_k1": 234, "lvl2_k2": "test"}

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

What's your opinion about this ? Is it a bug or an expected behaviour, and if so, how do you explain it ?

Thanks for sharing your knowledge !

Re: [GENERAL] JSON objects merge using || operator

From
Tom Lane
Date:
=?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

Re: [GENERAL] JSON objects merge using || operator

From
Mickaël Le Baillif
Date:
Le lun. 19 déc. 2016 à 18:52, Tom Lane <tgl@sss.pgh.pa.us> a écrit :
Since the Postgres parser doesn't have any special knowledge about
the meaning of the -> and || operators, it gives them the same precedence

Thanks for clarifying the situation here.

Do you have any reason for giving the same precedence on those operators ? A small survey among my colleagues and friends tends to believe that our human brain implicitly gives a greater priority to the '->' operator.  

Re: [GENERAL] JSON objects merge using || operator

From
Tom Lane
Date:
=?UTF-8?Q?Micka=C3=ABl_Le_Baillif?= <mickael.le.baillif@gmail.com> writes:
> Do you have any reason for giving the same precedence on those operators ?
> A small survey among my colleagues and friends tends to believe that our
> human brain implicitly gives a greater priority to the '->' operator.

PG's operator precedence rules were set long before the JSON types ever
existed.  Even if we wanted to treat -> specially, we couldn't for fear
of breaking existing queries that used custom operators named that.

            regards, tom lane