Search code examples
sqlprestodbt

Compare values in list of Json - Presto

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

  • 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;