PostgreSQL JsonB can't use IN operator on numbers

Question

I have a table with 4 columns and the following types.

|id          |businessId|type       |data |
|VARCHAR(100)|BIGINT    |VARCHAR(50)|jsonb|

My query needs to get all items with type 'XXX' and field from the data depID having one of the values 1 2 or 3.

This is how my query looks at the moment

select * from test_table where type='XXX' and data ->> 'depID' IN (1, 2, 3);

The above query give the following error:

[42883] ERROR: operator does not exist: text = integer Hint: No operator matches the given name and argument types. You might need to add explicit type casts.

depID is of type number in the json.

Same query works just fine for string values, for example:

select * from test_table where type='XXX' and data ->> 'loc' IN ('NYC', 'BST', 'None');

loc is of string type in the json.

I understand that it cant figure that the depID is of type number, but I am not sure how to specify it.

How to specify that the type of depID is number?

EDIT:

It turns out I was reading old specifications. I am using Postgres 11.4

The correct syntax is the following.

select * from test_table where type='XXX' and (data->>'depID'):NUMERIC IN (1, 2, 3);

The ->> operator has returns string. This explains why string fields work without casting.

Answer 1

The ->> operator has text as result data type, so it cannot return a number.

It would be possible to add a special access operator for numbers, but that would just clutter the ooerator namespace needlessly, since it can be done with a simple type cast:

CAST (data ->> 'depID' AS integer) IN (1, 2, 3)