Re: JSONB operator unanticipated behaviour

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(dot)klaver(at)aklaver(dot)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(at)rotamap(dot)net <mailto:brian(at)rotamap(dot)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(dot)klaver(at)aklaver(dot)com
>
>

--
Brian Mendoza
brian(at)rotamap(dot)net

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