Equivalent of LISTAGG that returns an ARRAY rather than delimited string?

Pieter_Sheth-Vo

Does Vertica have a function similar to LISTAGG or GROUP_CONCAT that returns an ARRAY type?

The relatively new Vertica ARRAY type is awesome, but I think LISTAGG only returns a string.

Imaginary query below:

  SELECT manufact, ARRAY_CONCAT(type)
  FROM car_sales 
  GROUP BY manufact

EDIT I see it's currently possible to use LISTAGG to return a string and subsequently split that to an array using STRING_TO_ARRAY, but wonder if there's an efficiency cost to this

WITH a AS (
   SELECT manufact, LISTAGG(manufact) as types
   FROM public.car_sales
   GROUP BY 1
)
SELECT 
    *, 
   STRING_TO_ARRAY(types::!VARCHAR USING PARAMETERS collection_delimiter=',') FROM a ;

https://forum.vertica.com/discussion/241946/how-to-access-listagg-or-group-concat-in-vertica-9-3
https://forum.vertica.com/discussion/208697/does-vertica-have-an-equivalent-to-mysqls-group-concat-function

SruthiA

Once you get the string using LISTAGG, I think you can use string_to_array function to convert that string to array. does it help?

https://www.vertica.com/docs/12.0.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Array/STRING_TO_ARRAY.htm

we have a function call ARRAY_CAT, but it concatenates only array data types. for more information, please visit the below link

https://www.vertica.com/docs/12.0.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Array/ARRAY_CAT.htm

Bryan_H

Could you IMPLODE your scalar column?

https://www.vertica.com/docs/12.0.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Array/IMPLODE.htm

marcothesane

This is how IMPLODE() works:
WITH
car_sales(manufact,"type") AS (
SELECT 'Morgan','Plus 6'
UNION ALL SELECT 'Morgan','Plus 4'
UNION ALL SELECT 'Morgan','Threewheeler'
UNION ALL SELECT 'Morgan','Super 3'
UNION ALL SELECT 'Lotus','Europa'
UNION ALL SELECT 'Lotus','Elise'
UNION ALL SELECT 'Lotus','Super 7'
UNION ALL SELECT 'Lotus','Formula 1'
UNION ALL SELECT 'Jaguar','XK 120'
UNION ALL SELECT 'Jaguar','XK 150'
UNION ALL SELECT 'Jaguar','E Type'
UNION ALL SELECT 'Jaguar','Formula E'
)
SELECT
manufact
, IMPLODE("type") AS typearray
FROM car_sales
GROUP BY manufact;
-- out manufact | typearray
-- out ----------+----------------------------------------------
-- out Morgan | ["Plus 6","Plus 4","Threewheeler","Super 3"]
-- out Jaguar | ["XK 120","XK 150","E Type","Formula E"]
-- out Lotus | ["Europa","Elise","Super 7","Formula 1"]
`