JSONB: Finding an element with a null attribute in array of objects

Question

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.

Evan Carroll · Accepted Answer · 2018-01-10 23:44:05Z

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)

answered Jan 10, 2018 at 23:44

Evan Carroll

65.8k50 gold badges263 silver badges512 bronze badges

Thanks Evan, but how does one do the inverse. Select where date is not null?

jprado
– jprado

2018-01-10 23:55:23 +00:00
Commented Jan 10, 2018 at 23:55
For example, retrieving name=test2 where its date is not null.

jprado
– jprado

2018-01-10 23:58:20 +00:00
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}]'

Evan Carroll
– Evan Carroll

2018-01-11 00:29:44 +00:00
Commented Jan 11, 2018 at 0:29

Add a comment |

Stack Exchange Network

JSONB: Finding an element with a null attribute in array of objects

1 Answer 1

Your Answer

Hot Network Questions

JSONB: Finding an element with a null attribute in array of objects

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions