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.

Answer 2

First check the JSON key is not null (using a single ->) its important this goes first.

Then check if the value is empty or not (using the double ->>) there's a few possible empty values so add checks for whatever you consider empty

so the asked query becomes.

SELECT data->>'property' 
FROM mytable 
WHERE data->'property' IS NOT NULL
    AND data->>'property' <> ''
    AND data->>'property' <> '{}'
    AND data->>'property' <> '[]';