Re: Querying JSON Lists

On 28.02.2017 17:33, Adrian Klaver wrote:
> 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

More details yes, but not really an explanation on the 'why'. Especially
not on 2) and 3). These feel like holes in the implementation.

Sven