• Empleos
  • Sobre nosotros
  • profesionales
    • Inicio
    • Empleos
    • Cursos y retos
  • empresas
    • Inicio
    • Publicar vacante
    • Nuestro proceso
    • Precios
    • Evaluaciones
    • Nómina
    • Blog
    • Comercial
    • Calculadora de salario

0

261
Vistas
Combine, dedupe, and sort an array in snowflake

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
almost 3 years ago · Juan Pablo Isaza
1 Respuestas
Responde la pregunta

0

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

almost 3 years ago · Juan Pablo Isaza Denunciar
Responde la pregunta
Encuentra empleos remotos

¡Descubre la nueva forma de encontrar empleo!

Top de empleos
Top categorías de empleo
Empresas
Publicar vacante Precios Nuestro proceso Comercial
Legal
Términos y condiciones Política de privacidad
© 2025 PeakU Inc. All Rights Reserved.

Andres GPT

Recomiéndame algunas ofertas
Necesito ayuda