Combine, dedupe, and sort an array in snowflake-CodePudding

I need to take an array of dates and add another array of dates to it. I need the array to remain in ascending sort order and contain any date only once. Below is the query and UDF I tried. Not beholden to this approach, but it's what I tried.

    SELECT 
       ARRAY_MERGE(ARRAY_COMPACT(c.array_visit_dates), ARRAY_COMPACT(d.array_visit_dates)) 
        AS array_visit_dates
    FROM myTable

CREATE OR REPLACE FUNCTION array_merge(old_date_list ARRAY, new_date_list ARRAY)
RETURNS ARRAY
  LANGUAGE JAVASCRIPT
  AS 
$$
    //var OLD_DATE_LIST = OLD_DATE_LIST.filter(date => date !== '').filter(date => date !== undefined)
    //var NEW_DATE_LIST = NEW_DATE_LIST.filter(date => date !== '').filter(date => date !== undefined)
    var full_list = OLD_DATE_LIST.concat(NEW_DATE_LIST);
    var dateset = new Set(full_list)
    var u_dates = [...new Set(dateset)]
    var u_dates = u_dates.sort()
    return u_dates.filter(date => date != '')
$$
;



 Error returned is:
        JavaScript execution error: Uncaught TypeError: Cannot read property 'concat' of undefined in ARRAY_MERGE at ' var full_list = OLD_DATE_LIST.concat(NEW_DATE_LIST);' position 34 stackstrace: ARRAY_MERGE line: 4

CodePudding user response：

You can do it with SQL:

select ARRAY_AGG( DISTINCT VALUE) WITHIN GROUP (ORDER BY VALUE) 
from LATERAL FLATTEN(ARRAY_CAT(array_construct('2021-02-03'::DATE, '2021-04-05'::DATE, '2021-10-06'::DATE), 
                               array_construct('2021-04-05'::DATE, '2020-02-04'::DATE, '2021-11-05'::DATE )));

Use ARRAY_CAT to merge arrays, flatten the items, and then re-construct the array using ARRAY_AGG