Re: BUG #11768: "jsonb ?" - not support int type operator

ua(dot)san(dot)alex(at)gmail(dot)com wrote:
> The following bug has been logged on the website:
>
> Bug reference: 11768
> Logged by: Alex
> Email address: ua(dot)san(dot)alex(at)gmail(dot)com
> PostgreSQL version: 9.4beta2
> Operating system: CentOS 7
> Description:
>
> Error - SELECT '[2, 5, 9]'::jsonb ? 5
>
> Support for numeric types is very important, without it, it is not
> convenient.

Not sure that would do what you want anyway:

alvherre=# select '[2,5,9]'::jsonb ? '5';
?column?
----------
f
(1 fila)

It only matches an element if it's of type string, as can be seen here:

alvherre=# select '[2,"5",9]'::jsonb ? '5';
?column?
----------
t
(1 fila)

I think primarily this operator was intended to look for keys in an
object (which is why it's called "exists"), which cannot be strings;
extending it to arrays might have been an afterthought. Example:

alvherre=# select '{"5":"cinco"}'::jsonb ? '5';
?column?
----------
t
(1 fila)

For definitional reasons, JSON keys cannot be numbers:

alvherre=# select '{5:"cinco"}'::jsonb ;
ERROR: invalid input syntax for type json
LÍNEA 1: select '{5:"cinco"}'::jsonb ;
^
DETALLE: Expected string or "}", but found "5".
CONTEXTO: JSON data, line 1: {5...

I think you might be looking for the "contains" operator:

alvherre=# select '[2,5,9]'::jsonb @> '5';
?column?
----------
t
(1 fila)

This one only accepts a jsonb as right argument, so it won't work with a
bare integer either:

alvherre=# \do @>
Listado de operadores
Esquema | Nombre | Tipo arg izq | Tipo arg der | Tipo resultado | Descripción
------------+--------+--------------+--------------+----------------+-------------
...
pg_catalog | @> | jsonb | jsonb | boolean | contains
...
(13 filas)

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services