0

With the following table in psql 10:

CREATE TABLE logs(id serial, data JSONB); 
INSERT INTO logs VALUES
  (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.

Improve this question

1 Answer 1

Reset to default
1

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
3
  • Thanks Evan, but how does one do the inverse. Select where date is not null? Commented Jan 10, 2018 at 23:55
  • For example, retrieving name=test2 where its date is not null. Commented Jan 10, 2018 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}]' Commented Jan 11, 2018 at 0:29

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.