How to check using Presto that end_date > start_date in every Json of the list. Time in epoch milliseconds. All there lists are in a column name 'dates'
[{end_date=1662960600000, start_date=1662984000000}, {end_date=1663047000000, start_date=1663045200000}, {end_date=1663133400000, start_date=1663131600000}]
Based on provided output your data looks like array of maps or rows, so you can handle it accordingly with array functions:
-- sample data
with test_data (dates) AS (
VALUES (array[
map(array['end_date','start_date'], array[1662960600000, 1662984000000])
, map(array['end_date','start_date'], array[1662984000000, 1662960600000])])
)
-- query
select all_match(dates, m -> m['end_date'] > m['start_date']) all_start_earlier_then_end
from test_data;
Output:
| all_start_earlier_then_end |
|---|
| false |
rows version will look like:
select all_match(dates, m -> m.end_date > m.start_date) all_start_earlier_then_end
from test_data;
If all_match is not available (due to older version) you can workaround with filter and cardinality:
select cardinality(filter(dates, m -> m.end_date < m.start_date)) = 0 all_start_earlier_then_end
from test_data;