how to query date in json?

Question

create tabel test(json jsonb);
insert into test values('{"graductionDate": "Jun  1 2015 12:00AM"}')
insert into test values('{"graductionDate": "Jun  1 2016 12:00AM"}')

query result is incorrect:

select * from test where json>'{"graductionDate":"20151001 00:00"}'

I want get data with graductionDate after 20150101. But the above code gets all rows.

Answer 1

http://www.postgresql.org/docs/current/static/functions-json.html

Not having used json objects before I would guess you need to extract the date field from the object, treat it as a date and then compare it to another date.

select * 
  from test 
 where ((json->>'graductionDate')::timestamp) > ('20151001 00:00':: timestamp);

I havent tested this code.

---

Edit

From your comment it sounds like the ->> operator treats null as the empty string ''. You can convert '' to null using the nullif() function:

select * 
  from test 
 where (nullif(json->>'graductionDate', '')::timestamp) > ('20151001 00:00':: timestamp);

This code is still untested.