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."

Are you sure you don't use 9.4? In 9.4 the ->> operator has lower precedence than the IS [NOT] NULL (so your query is parsed as data ->> ('property' IS NOT NULL)). I have no 9.5 instance right now to test, but it seems (from @Patrick 's answer) that this might be an improvement introduced in 9.5. In 9.4 the simplest workaround is to use parenthesis: (data->>'property') IS NOT NULL. — pozs
– pozs, Commented Jan 25, 2016 at 16:27
Change is indeed happened: 9.4 vs. 9.5 (-> and ->> is within the (any other) operator). — pozs
– pozs, Commented Jan 25, 2016 at 16:34

Patrick · Accepted Answer · 2016-01-25 05:43:55Z

25

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.

edited Jan 25, 2016 at 5:43

answered Jan 25, 2016 at 5:28

Patrick

33k7 gold badges73 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

sheldonkreger Over a year ago

Unfortunately I can't post the JSON here but I can make a few comments. 1. Other queries on the JSONB work well. 2. It looks like most the data in this particular attribute of the JSON is defined as <null>

sheldonkreger Over a year ago

I don't think my JSON property is being set to null in the way you posted. Is there a way to check for an empty string?

sunsetjunks Over a year ago

+ for mentioning -> instead of a text type cast (->>) as most people just copy the examples with type casting when they really do not need the overhead!

Draken · Accepted Answer · 2021-11-02 14:47:55Z

6

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' <> '[]';

edited Nov 2, 2021 at 14:47

Draken

3,19614 gold badges38 silver badges56 bronze badges

answered Nov 1, 2021 at 21:43

jerboa-io

2013 silver badges2 bronze badges

Collectives™ on Stack Overflow

SELECT values which are not null from JSONB field in Postgres

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related