Get records where json column key is null

Question

I have table users with json column details.

I want to fetch all user records where details["email"] is null or email key doesn't exist.

This doesn't work:

SELECT users.* FROM users where details->'email' IS NOT NULL;

ERROR: operator does not exist: json -> boolean

Answer 1

use brackets (). Looks like compiler tries to see it like details->('email' IS NOT NULL). So you can fix it like this:

select * from users where (details->'email') is not null 

sql fiddle demo

actually, to get records where details["email"] is null or email key doesn't exist, you can use this query:

select * from users where (details->>'email') is null 

as described in this answer.