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