Re: JSONB operator unanticipated behaviour

[Brian Mendoza <brian@xxxxxxxxxxx>]

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@xxxxxxxxxxx> 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@xxxxxxxxxxx <mailto:brian@xxxxxxxxxxx>
>
> 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@xxxxxxxxxxx

--

Brian Mendoza

brian@xxxxxxxxxxx

Rotamap
www.rotamap.net
020 7631 1555
3 Tottenham Street London W1T 2AF
Registered in England No. 04551928