Postgres JSONB date condition true for all entries of an array

Question

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

Which Postgres version are you using?

user330315
– user330315

2020-03-05 11:03:15 +00:00
Commented Mar 5, 2020 at 11:03 — user330315
– user330315, Commented Mar 5, 2020 at 11:03
@a_horse_with_no_name 9.6

n.st
– n.st

2020-03-05 11:22:18 +00:00
Commented Mar 5, 2020 at 11:22 — n.st
– n.st, Commented Mar 5, 2020 at 11:22

user330315 · Accepted Answer · 2020-03-05 11:14:35Z

2

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

answered Mar 5, 2020 at 11:14

user330315

Sign up to request clarification or add additional context in comments.

3 Comments

n.st Over a year ago

That gives me the error cannot extract elements from an object

user330315 Over a year ago

Then apparently your JSON doesn't contain an array - contrary to what you wrote in your question dbfiddle.uk/…

n.st Over a year ago

you're right, there are some entries in my DB where there's no data so its just {}, guess I'll need to filter those out

Collectives™ on Stack Overflow

Postgres JSONB date condition true for all entries of an array

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related