Re: Querying JSON Lists

On 03/02/2017 01:09 PM, Sven R. Kunze wrote:
>
>
> 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)

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

"-- Object containing pairs of keys and values
-- Note that object keys must always be quoted strings
SELECT '{"bar": "baz", "balance": 7.77, "active": false}'::json;"

Which I believe comes from:

https://tools.ietf.org/html/rfc7159

"4. Objects

An object structure is represented as a pair of curly brackets
surrounding zero or more name/value pairs (or members). A name is a
string. A single colon comes after each name, separating the name
from the value. A single comma separates a value from a following
name. The names within an object SHOULD be unique.
....
"

https://www.postgresql.org/docs/9.6/static/functions-json.html

As to why it works on JSON arrays:

Table 9-43. Additional jsonb Operators
"
? text Does the string exist as a top-level key within the JSON value?
"

So to be picky it not does call out JSON object it says JSON value. And
right above the table:

" For a full description of jsonb containment and existence semantics,
see Section 8.14.3. Section 8.14.4 describes how these operators can be
used to effectively index jsonb data."

As to how that behavior was decided on I have no idea, it just is.

and 3). These feel like holes in the implementation.

As to this:

test=> select '["12","34","45"]'::jsonb @> '"12"';
?column?
----------
t

"
As a special exception to the general principle that the structures must
match, an array may contain a primitive value:

-- This array contains the primitive string value:
SELECT '["foo", "bar"]'::jsonb @> '"bar"'::jsonb;

-- This exception is not reciprocal -- non-containment is reported here:
SELECT '"bar"'::jsonb @> '["bar"]'::jsonb; -- yields false
"

Though there looks to be some implicit casting going on:

test=> select '["12","34","45"]'::jsonb @> '"12"'::text;

ERROR: operator does not exist: jsonb @> text

LINE 1: select '["12","34","45"]'::jsonb @> '"12"'::text;

to get '"12"' to be '"12"'::jsonb.

As to why, I don't know.

>
> Sven
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com