Search code examples
postgresqljsonb

Postgres JSONB date condition true for all entries of an array

I have a JSONB that looks something like this

[{
"foo":"bar",
"date":"2020-01-01"
},
{
"foo":"bar",
"date":"2020-02-03"
},
{
"foo":"bar",
"date":"2020-01-02"
}]

I need a query to return true if ALL of the "date"s are less than 1 year ago. I have looked at the postgres JBON documentation and the only thing sort of fitting I found was using ?& but I'm not just trying to compare strings but dates that are strings so I am kind of lost here

Solution

  • You will need to iterate over all elements and then convert the strings to dates in order to be able to compare them. 

    select .... other columns ...., 
       current_date - interval '1 year' < all (select (x.entry ->> 'date')::date
                                               from jsonb_array_elements(t.data) as x(entry))
from the_table t