From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Mickaël Le Baillif <mickael(dot)le(dot)baillif(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: JSON objects merge using || operator |
Date: | 2016-12-19 17:52:28 |
Message-ID: | 27673.1482169948@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
=?UTF-8?Q?Micka=C3=ABl_Le_Baillif?= <mickael(dot)le(dot)baillif(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2016-12-19 17:55:15 | Re: Is there a way to Send attachments with email using pgmail postgreSQl? |
Previous Message | Pavel Stehule | 2016-12-19 17:36:49 | Re: Is there a way to Send attachments with email using pgmail postgreSQl? |