pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedJSONB operator unanticipated behaviour
7+ messages / 3 participants
[nested] [flat]
* JSONB operator unanticipated behaviour
@ 2023-05-18 13:17 Brian Mendoza <brian@rotamap.net>
0 siblings, 1 reply; 7+ messages in thread
From: Brian Mendoza @ 2023-05-18 13:17 UTC (permalink / raw)
To: pgsql-general
Hello,
I have encountered unanticipated behaviour with a JSONB operator, and
wanted to make sure I am not misunderstanding its intended use.
When using the @> operator, I get this result.
select '{"a": [1]}'::jsonb @> '{"a": []}'::jsonb;
?column?
----------
t
(1 row)
However, given the phrasing in the documentation, I would have expected
False.
"Does the left JSON value contain the right JSON path/value entries at the
top level?"
Particularly given the following:
select '[1]'::jsonb = '[]'::jsonb;
?column?
----------
f
(1 row)
So the keys are the same, the values (when compared directly) are not, but
@> returns True. Have I misunderstood the usage of the operator?
The above queries have been run on postgres 14, if that helps.
--
Brian Mendoza
brian@rotamap.net
Rotamap
www.rotamap.net
020 7631 1555
3 Tottenham Street London W1T 2AF
Registered in England No. 04551928
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: JSONB operator unanticipated behaviour
@ 2023-05-18 14:41 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Brian Mendoza <brian@rotamap.net>
0 siblings, 1 reply; 7+ messages in thread
From: Adrian Klaver @ 2023-05-18 14:41 UTC (permalink / raw)
To: Brian Mendoza <brian@rotamap.net>; pgsql-general
On 5/18/23 06:17, Brian Mendoza wrote:
> Hello,
>
> I have encountered unanticipated behaviour with a JSONB operator, and
> wanted to make sure I am not misunderstanding its intended use.
>
> When using the @> operator, I get this result.
>
> select '{"a": [1]}'::jsonb @> '{"a": []}'::jsonb;
> ?column?
> ----------
> t
> (1 row)
>
> However, given the phrasing in the documentation, I would have expected
> False.
>
> "Does the left JSON value contain the right JSON path/value entries at
> the top level?"
>
> Particularly given the following:
>
> select '[1]'::jsonb = '[]'::jsonb;
> ?column?
> ----------
> f
> (1 row)
>
> So the keys are the same, the values (when compared directly) are not,
> but @> returns True. Have I misunderstood the usage of the operator?
>
> The above queries have been run on postgres 14, if that helps.
Have you looked at the containment examples?:
https://www.postgresql.org/docs/current/datatype-json.html#JSON-CONTAINMENT
I'm thinking this:
-- A top-level key and an empty object is contained:
SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"foo": {}}'::jsonb;
and/or this
"The general principle is that the contained object must match the
containing object as to structure and data contents, possibly after
discarding some non-matching array elements or object key/value pairs
from the containing object. "
applies.
>
> --
> Brian Mendoza
> brian@rotamap.net <mailto:brian@rotamap.net>
>
> Rotamap
> www.rotamap.net <https://www.rotamap.net;
> 020 7631 1555
> 3 Tottenham Street London W1T 2AF
> Registered in England No. 04551928
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: JSONB operator unanticipated behaviour
@ 2023-05-18 15:09 Brian Mendoza <brian@rotamap.net>
parent: Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 1 reply; 7+ messages in thread
From: Brian Mendoza @ 2023-05-18 15:09 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: pgsql-general
Ah, yes, that seem to be the explanation!
So it would seem that indeed it was my misunderstanding of the operator.
select '{"a": [1,2]}'::jsonb @> '{"a": [1]}'::jsonb;
?column?
----------
t
(1 row)
select '{"a": [1,2]}'::jsonb @> '{"a": [2,1,2]}'::jsonb;
?column?
----------
t
(1 row)
I was not aware of "possibly after discarding some non-matching array
elements or object key/value pairs from the containing object. But remember
that the order of array elements is not significant when doing a
containment match, and duplicate array elements are effectively considered
only once." and was expecting array equality to be the comparison. Good to
know!
Many thanks
On Thu, 18 May 2023 at 15:41, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:
> On 5/18/23 06:17, Brian Mendoza wrote:
> > Hello,
> >
> > I have encountered unanticipated behaviour with a JSONB operator, and
> > wanted to make sure I am not misunderstanding its intended use.
> >
> > When using the @> operator, I get this result.
> >
> > select '{"a": [1]}'::jsonb @> '{"a": []}'::jsonb;
> > ?column?
> > ----------
> > t
> > (1 row)
> >
> > However, given the phrasing in the documentation, I would have expected
> > False.
> >
> > "Does the left JSON value contain the right JSON path/value entries at
> > the top level?"
> >
> > Particularly given the following:
> >
> > select '[1]'::jsonb = '[]'::jsonb;
> > ?column?
> > ----------
> > f
> > (1 row)
> >
> > So the keys are the same, the values (when compared directly) are not,
> > but @> returns True. Have I misunderstood the usage of the operator?
> >
> > The above queries have been run on postgres 14, if that helps.
>
> Have you looked at the containment examples?:
>
> https://www.postgresql.org/docs/current/datatype-json.html#JSON-CONTAINMENT
>
> I'm thinking this:
>
> -- A top-level key and an empty object is contained:
> SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"foo": {}}'::jsonb;
>
> and/or this
>
> "The general principle is that the contained object must match the
> containing object as to structure and data contents, possibly after
> discarding some non-matching array elements or object key/value pairs
> from the containing object. "
>
> applies.
>
> >
> > --
> > Brian Mendoza
> > brian@rotamap.net <mailto:brian@rotamap.net>
> >
> > Rotamap
> > www.rotamap.net <https://www.rotamap.net;
> > 020 7631 1555
> > 3 Tottenham Street London W1T 2AF
> > Registered in England No. 04551928
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>
>
--
Brian Mendoza
brian@rotamap.net
Rotamap
www.rotamap.net
020 7631 1555
3 Tottenham Street London W1T 2AF
Registered in England No. 04551928
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: JSONB operator unanticipated behaviour
@ 2023-05-18 15:46 Tom Lane <tgl@sss.pgh.pa.us>
parent: Brian Mendoza <brian@rotamap.net>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2023-05-18 15:46 UTC (permalink / raw)
To: Brian Mendoza <brian@rotamap.net>; +Cc: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general
Brian Mendoza <brian@rotamap.net> writes:
> Ah, yes, that seem to be the explanation!
> So it would seem that indeed it was my misunderstanding of the operator.
You seem to be reading some fairly old version of the documentation.
The extended definition that Adrian mentions has been there for
awhile, but the JSON operator table didn't link to it before v13.
(I agree that the "top level" bit was just wrong, but it's gone.)
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: JSONB operator unanticipated behaviour
@ 2023-05-18 15:57 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Adrian Klaver @ 2023-05-18 15:57 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Brian Mendoza <brian@rotamap.net>; +Cc: pgsql-general
On 5/18/23 08:46, Tom Lane wrote:
> Brian Mendoza <brian@rotamap.net> writes:
>> Ah, yes, that seem to be the explanation!
>> So it would seem that indeed it was my misunderstanding of the operator.
>
> You seem to be reading some fairly old version of the documentation.
> The extended definition that Adrian mentions has been there for
> awhile, but the JSON operator table didn't link to it before v13.
> (I agree that the "top level" bit was just wrong, but it's gone.)
Can you elaborate on gone and/or wrong as I see it in the 15 and devel
documentation.
>
> regards, tom lane
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: JSONB operator unanticipated behaviour
@ 2023-05-18 16:36 Tom Lane <tgl@sss.pgh.pa.us>
parent: Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2023-05-18 16:36 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: Brian Mendoza <brian@rotamap.net>; pgsql-general
Adrian Klaver <adrian.klaver@aklaver.com> writes:
> On 5/18/23 08:46, Tom Lane wrote:
>> You seem to be reading some fairly old version of the documentation.
>> The extended definition that Adrian mentions has been there for
>> awhile, but the JSON operator table didn't link to it before v13.
>> (I agree that the "top level" bit was just wrong, but it's gone.)
> Can you elaborate on gone and/or wrong as I see it in the 15 and devel
> documentation.
In v12 (and probably earlier, didn't look) Table 9.45 defines @> as
"Does the left JSON value contain the right JSON path/value entries
at the top level?" [1].
regards, tom lane
[1] https://www.postgresql.org/docs/12/functions-json.html
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: JSONB operator unanticipated behaviour
@ 2023-05-18 17:41 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Adrian Klaver @ 2023-05-18 17:41 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Brian Mendoza <brian@rotamap.net>; pgsql-general
On 5/18/23 09:36, Tom Lane wrote:
> Adrian Klaver <adrian.klaver@aklaver.com> writes:
>> On 5/18/23 08:46, Tom Lane wrote:
>>> You seem to be reading some fairly old version of the documentation.
>>> The extended definition that Adrian mentions has been there for
>>> awhile, but the JSON operator table didn't link to it before v13.
>>> (I agree that the "top level" bit was just wrong, but it's gone.)
>
>> Can you elaborate on gone and/or wrong as I see it in the 15 and devel
>> documentation.
>
> In v12 (and probably earlier, didn't look) Table 9.45 defines @> as
> "Does the left JSON value contain the right JSON path/value entries
> at the top level?" [1].
Alright I get it now.
>
> regards, tom lane
>
> [1] https://www.postgresql.org/docs/12/functions-json.html
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2023-05-18 17:41 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-05-18 13:17 JSONB operator unanticipated behaviour Brian Mendoza <brian@rotamap.net>
2023-05-18 14:41 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-05-18 15:09 ` Brian Mendoza <brian@rotamap.net>
2023-05-18 15:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-05-18 15:57 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-05-18 16:36 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-05-18 17:41 ` Adrian Klaver <adrian.klaver@aklaver.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox