Compare elements of a column containing 2D arrays with another column postgresql

nakkhatra-opti 0
Viewed 20

My table looks like this. The dates_array column is basically the window function of array_agg(date_start, date_end) over (mid rows between unbounded preceding and unbounded following) Now I want to check for each row, if (date_start> dates_array[0][0] and date_end < dates_array[0][1]) or (date_start> dates_array[1][0] and date_end < dates_array[1][1]) and so on for the rest of the 1D arrays inside date_start and set a boolean if any of these is true. I thought of using unnest, but can't think of any way to use that. Any solution?

cid mid date_start  date_end    dates_array
1   A   2019-05-17  2020-05-17  {{2019-05-17,2020-05-17},{2020-05-17,2021-08-17}}
2   A   2020-05-17  2021-08-17  {{2019-05-17,2020-05-17},{2020-05-17,2021-08-17}}
3   B   2019-05-12  2020-05-12  {{2019-05-12,2020-05-12}}
4   C   2019-07-01  2020-07-01  {{2019-07-01,2020-07-01},{2020-07-01,2021-07-01},{2021-07-01,2021-08-01},{2022-07-01,2023-07-01}}
5   C   2020-07-01  2021-07-01  {{2019-07-01,2020-07-01},{2020-07-01,2021-07-01},{2021-07-01,2021-08-01},{2022-07-01,2023-07-01}}
multidimensional-array postgresql arrays
1 Answers

You can do it write your own function. For example:

CREATE OR REPLACE FUNCTION check_exists(datestart date, dateend date, datearray date[])
RETURNS boolean
LANGUAGE plpgsql
AS $function$
declare 
    m date[];
BEGIN
    FOREACH m SLICE 1 IN ARRAY datearray
    LOOP
        if ((datestart > m[1]) and (dateend < m[2])) then 
            return true;
        end if; 
    END LOOP;

    return false;
END;
$function$
;

This function will be return true when only one of conditions will be true. You can change logic of this function to needed you.

Sample of using function:

select date_start, date_end, check_exists(date_start, date_end, dates_array) from your_table
Ramin Faracov0
Related