SELECT values which are not null from JSONB field in Postgres

Question

I am unable to select non-null values from a property inside a JSONB field in Postgres 9.5

SELECT data->>'property' FROM mytable WHERE data->>'property' IS NOT NULL;

I also tried using NOTNULL.

I receive error 42883 when I run either of these. "ERROR: Operator does not exist. JSONB->>boolean Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts."

Answer 1

I quickly tested your question and found no problem:

patrick@brick:~$ psql -d test
psql (9.5.0)
Type "help" for help.

test=# CREATE TABLE mytable (id serial PRIMARY KEY, data jsonb);
CREATE TABLE
test=# INSERT INTO mytable (data) VALUES
('{"ip": "192.168.0.1", "property": "router"}'),
('{"ip": "127.0.0.1", "property": "localhost"}'),
('{"ip": "192.168.0.15", "property": null}');
INSERT 0 3
test=# SELECT * FROM mytable;
 id |                     data
----+----------------------------------------------
  1 | {"ip": "192.168.0.1", "property": "router"}
  2 | {"ip": "127.0.0.1", "property": "localhost"}
  3 | {"ip": "192.168.0.15", "property": null}
(3 rows)

test=# SELECT data->>'property' FROM mytable WHERE data->>'property' IS NOT NULL;
 ?column?
-----------
 router
 localhost
(2 rows)

Note that in jsonb a NULL value should be specified precisely so on input (as in the sample above), not in some quoted version. If the value is not NULL but an empty string or something like '<null>' (a string) then you should adapt your test to look for that: WHERE data->>'property' = ''. If this is the case you could consider using jsonb_set() to set such values to a true json null.

Incidentally, you could also do:

SELECT data->>'property' FROM mytable WHERE data->'property' IS NOT NULL;

i.e. test the jsonb value for NULL rather than its cast to text. More efficient, certainly on larger tables. This obviously only works on true nulls.