With the following table in psql 10:

CREATE TABLE logs(id serial, data JSONB); 
  (1, '[{"name":"test1","date":"2018-01-09"},{"name":"test2","date":"2018-01-10"}]'),
  (2, '[{"name":"test3","date":"2018-01-10"},{"name":"test4","date":null}]');

How do I find row #2 by querying the date=null value in the array?

I already know how to query by name to find row#2:

AND data @> '[{"name": "test4"}]'

But I don't know how you can query for rows with or without a null date attribute.


Like this,

SELECT * FROM logs WHERE data @> '[{"date":null}]';
 id |                                    data                                    
  2 | [{"date": "2018-01-10", "name": "test3"}, {"date": null, "name": "test4"}]
(1 row)
| improve this answer | |
  • Thanks Evan, but how does one do the inverse. Select where date is not null? – jprado Jan 10 '18 at 23:55
  • For example, retrieving name=test2 where its date is not null. – jprado Jan 10 '18 at 23:58
  • @jprado that's not going to work well: that's not what JSON was designed for. For something easy though you could do SELECT * FROM logs WHERE NOT EXISTS ( SELECT 1 FROM logs WHERE data @> '[{"date":null}]' – Evan Carroll Jan 11 '18 at 0:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.