CODEWITHSUNDEEP
postgresqljsonb
n.st
n.st

Reputation: 515

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

Upvotes: 0

Views: 404

Answers (1)

user330315
user330315

Reputation:

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

Upvotes: 2

Related Questions