|
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. -- 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? Related docs: https://www.postgresql.org/docs/9.5/static/datatype-json.html Regards, Sven |
