Re: [GENERAL] Querying JSON Lists

On 02/26/2017 03:26 AM, Sven R. Kunze wrote:
> Hello everyone,
>
> playing around with jsonb and coming from this SO question
> http://stackoverflow.com/questions/19925641/check-if-a-postgres-json-array-contains-a-string
> I wonder why PostgreSQL behaves differently for text and integers on the
> ? and @> operators.
>
>
> Let's have a look at 4 different but similar queries:
>
> -- A) ? + text
> select '{"food": ["12","34","45"]}'::jsonb->'food' ? '12';
>  ?column?
> ----------
>  t
>
> -- B) ? + integer
> select '{"food": [12,34,45]}'::jsonb->'food' ? 12;
> ERROR:  operator does not exist: jsonb ? integer
> LINE 1: select '{"food": [12,34,45]}'::jsonb->'food' ? 12;
>                                                      ^
> HINT:  No operator matches the given name and argument type(s). You
> might need to add explicit type casts.

https://www.postgresql.org/docs/9.6/static/datatype-json.html#JSON-CONTAINMENT

"jsonb also has an existence operator, which is a variation on the theme
of containment: it tests whether a string (given as a text value)
appears as an object key or array element at the top level of the jsonb
value. These examples return true except as noted

-- String exists as array element:
SELECT '["foo", "bar", "baz"]'::jsonb ? 'bar';
"

>
> -- C) @> + text
> select '{"food": ["12","34","45"]}'::jsonb->'food' @> '["12"]',
> '{"food": ["12","34","45"]}'::jsonb->'food' @> '"12"', '{"food":
> ["12","34","45"]}'::jsonb->'food' @> '12';
>  ?column? | ?column? | ?column?
> ----------+----------+----------
>  t        | t        | f
>
> -- D) @> + integer
> select '{"food": [12,34,45]}'::jsonb->'food' @> '[12]', '{"food":
> [12,34,45]}'::jsonb->'food' @> '12';--, '{"food":
> [12,34,45]}'::jsonb->'food' @> 12;
>  ?column? | ?column?
> ----------+----------
>  t        | t
>
>
> Now my questions:
>
> 1) Why does A) work? Docs tells us that ? works for keys, not values.
> 2) Why does B) not work although A) works?
> 3) Why do the variants without the brackets on the right side of @> work
> in C) and D)? Is there json data where their results differ from the
> ones with the brackets?
> 4) What is the recommended way of testing inclusion in json lists?

I have not worked through your examples, but I suspect the answer's lie
here:

https://www.postgresql.org/docs/9.6/static/datatype-json.html#JSON-CONTAINMENT

8.14.3. jsonb Containment and Existence

>
> Related docs: https://www.postgresql.org/docs/9.5/static/datatype-json.html
>
> Regards,
> Sven


--
Adrian Klaver
adrian.klaver@aklaver.com