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

ua.san.alex@gmail.com wrote:
> The following bug has been logged on the website:
>
> Bug reference:      11768
> Logged by:          Alex
> Email address:      ua.san.alex@gmail.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