CODEWITHSUNDEEP
postgresqlset-returning-functions
Midorina
Midorina

Reputation: 21

Using set-returning functions in WHERE clauses

SELECT * from users where server_id=$1 and $2=ANY(json_object_keys(contacts));

ERROR:  set-returning functions are not allowed in WHERE

contacts is a json column and I need to find rows that has $2 in their contacts' keys.

Example contacts column: {"90144": 0, "89915": 0}

If $2 is 90144, query should return every row that contain 90144 in their contacts' keys, like in the example. How could I achieve this? Thanks in advance.

Upvotes: 0

Views: 587

Answers (1)

jjanes
jjanes

Reputation: 44227

Use the ? operator.

SELECT * from users where server_id=$1 and contacts::jsonb ? $2;

Upvotes: 1

Related Questions