How to use jsonb column in WHERE IN in Postgres

Question

I have the following two tables in Postgres database

employee

emp_id	usersname	first_name	email	country
251030	username1	first_name_1	[email protected]	USA
251035	username2	first_name_2	[email protected]	Canada
251036	username3	first_name_3	[email protected]	Australia

employee_notifications

id	notification_title	users(jsonb)
1	title1	[251030,251035]
2	title2	[251030,251035]
3	titl3	[251030,251035]

Now I need to select the employee details from the employee table where the values are in users array in employee_notifications table. The users column is of type jsonb

I am using the following query here :

SELECT emp_id,username FROM employee 
WHERE emp_id IN (
    SELECT users FROM employee_notifications WHERE id =3
)

When I use this, I am getting an error ERROR: operator does not exist: character varying = jsonb. Also please note that my emp_id column is of type varchar

How can I convert this jsonb array to a list or something and use it in the IN condition ?

Answer 1

users is an array of IDs, so you can't compare it with a single integer value. You will need to use an EXISTS condition:

SELECT e.emp_id, e.usersname
FROM employee e
WHERE EXISTS (
    SELECT * 
    FROM employee_notifications en
    WHERE en.users @> to_jsonb(e.emp_id)
)

If employee.emp_id is indeed a varchar (why?), and your JSONB contains "integer" values like this :[251030,251035] (why if emp_id is a string?) you need to cast the emp_id to an integer before converting it to a JSONB value:

SELECT e.emp_id, e.usersname
FROM employee e
WHERE EXISTS (
    SELECT * 
    FROM employee_notifications en
    WHERE en.users @> to_jsonb(e.emp_id::int)
)