How can I filter JSON column in Postgresql?

Question

Lets say that we have simple table,

• id - user id
• rooms - number of rooms which the user is searching in JSON type.

id rooms
1 ["3.0","4.0"]
2 ["3.0"]
3 ["1.0"]

How can I filter for example user id which is searching 3 rooms?

I know that code like:

SELECT
    id
FROM 
    example_column
WHERE
rooms::text LIKE '%3.0%'

is working, but i'm lookig for more gentle method.

Answer 1

You can use the "exists" ? operator to test for elements in an array:

SELECT id
FROM the_table
WHERE rooms ? '3.0';

The operator is only available for jsonb values, if you are really using a json column, you need to cast it:

WHERE rooms::jsonb ? '3.0';