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

score 63 · Accepted Answer · edited May 23 '17 at 12:26

63

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

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.

edited May 23 '17 at 12:26

Community

answered May 28 '14 at 13:13

Roman Pekar

1

Exactly - brackets were missing. Thanks. – morgoth May 28 '14 at 13:20
1

Just a word for posterity: brackets isn't needed on Postgres version starting from 9.5. – Michel Milezzi Jun 21 '17 at 18:14

score 10 · Answer 2 · answered May 28 '14 at 13:21

10

You need to use the ->> operator for that:

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

Because the -> operator returns 'null'::json (and not sql NULL) if the key is exists but with a json null value.

answered May 28 '14 at 13:21

pozs

1

Actually the problem was with missing brackets as answered in http://stackoverflow.com/a/23912628/298554 – morgoth May 28 '14 at 13:22
I tried both with `->` and `->>` - it always return correct records. – morgoth May 28 '14 at 13:23
1

@morgoth That was just the syntactical problem. If the one wants to treat `'null'::json` values the same as sql's `NULL` they should use the `->>` operator over the `->`. See the difference in my fiddle. – pozs May 28 '14 at 13:24
Missing brackets was the problem for me too. – Dom Jan 20 '15 at 02:29

2 Answers2