Querying JSON Lists

From: "Sven R(dot) Kunze" <srkunze(at)mail(dot)de>
To: pgsql-general(at)postgresql(dot)org
Subject: Querying JSON Lists
Date: 2017-02-26 11:26:03
Message-ID: c5dbdead-2795-d1ae-70fa-04cdf699ed42@mail.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Nicolas Paris 2017-02-26 12:52:55 Full Text Search combined with Fuzzy
Previous Message Sven R. Kunze 2017-02-26 10:09:04 ERROR: functions in index expression must be marked IMMUTABLE