From: | Mickaël Le Baillif <mickael(dot)le(dot)baillif(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | JSON objects merge using || operator |
Date: | 2016-12-19 13:36:49 |
Message-ID: | CALtLrz+sQJEOdx4z62UfrkHsK+WsRkpHPiY7JRruQih+YYnEJA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 !
From | Date | Subject | |
---|---|---|---|
Next Message | Melvin Davidson | 2016-12-19 14:30:26 | Re: pgAdmin 4 - auto disconnect |
Previous Message | Paolo Saudin | 2016-12-19 12:28:09 | pgAdmin 4 - auto disconnect |