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 ?

Why using JSON for data to be queried? Far from optimal performance design.. — Serg
– Serg, Commented Feb 4, 2022 at 7:22
Use a "junction table" instead of a JSON array, then it will be a simple join. — Laurenz Albe
– Laurenz Albe, Commented Feb 4, 2022 at 7:41
Storing numbers in varchar columns is also a bad decision. — user330315
– user330315, Commented Feb 4, 2022 at 7:53
I agree with all the comments here regarding the design. But the table is already existing there and I am not able to change the design as of now as it requires a lot of effort to refactor the complete application. This will be done later, but as of now, I need some workaround to get eh values. — Happy Coder
– Happy Coder, Commented Feb 4, 2022 at 8:56

score 0 · Accepted Answer · 2022-02-04 09:21:51Z

0

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)
)

edited Feb 4, 2022 at 9:21

answered Feb 4, 2022 at 7:53

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

Happy Coder Over a year ago

This is just returning the column names in the select statement and there is no values even though I have matching values there in the array and the employee table.

user330315 Over a year ago

@HappyCoder: see my edit you are using inconsistent data types all over so you need to cast things back and forth

Collectives™ on Stack Overflow

How to use jsonb column in WHERE IN in Postgres

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related