Thread: JSONB operator unanticipated behaviour
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)
?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)
?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.netRotamap
www.rotamap.net
020 7631 1555
3 Tottenham Street London W1T 2AF
Registered in England No. 04551928
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
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)
?column?
----------
t
(1 row)
select '{"a": [1,2]}'::jsonb @> '{"a": [2,1,2]}'::jsonb;
?column?
----------
t
(1 row)
?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.netRotamap
www.rotamap.net
020 7631 1555
3 Tottenham Street London W1T 2AF
Registered in England No. 04551928
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
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
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
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