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@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
