数组的所有条目的Postgres JSONB日期条件为true [英] Postgres JSONB date condition true for all entries of an array

问题描述

我有一个看起来像这样的JSONB

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"
}]

如果所有日期"都小于1年前，我需要一个查询以返回true.我看过 postgres JBON文档和唯一的我发现正在使用?&但我不仅要比较字符串，还要比较字符串的日期，所以我在这里有点迷失了

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

推荐答案

您将需要遍历所有元素，然后将字符串转换为日期，以便能够进行比较.

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

这篇关于数组的所有条目的Postgres JSONB日期条件为true的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！