Convert ARRAY to SET?

Pieter_Sheth-Vo

Is it possible to deduplicate an ARRAY value in Vertica?
I imagine there must be a function to convert an ARRAY value to a SET value,
and think it should be allowed to call EXPLODE on a set.

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

SruthiA

We dont need any function to convert array to set.. you can just do type casting as shown in the below documentation

https://www.vertica.com/docs/10.1.x/HTML/Content/Authoring/SQLReferenceManual/DataTypes/SET.htm

You can explicitly convert an array to a set by casting, as in the following example:

=> SELECT ARRAY[1, 5, 2, 6, 3, 0, 6, 4]::SET[INT];

set

[0,1,2,3,4,5,6]
(1 row)

For explode, it requires array as an input and not set.. please check the below link

https://www.vertica.com/docs/10.1.x/HTML/Content/Authoring/AnalyzingData/Queries/Arrays.htm?zoom_highlight=explode array#4

Consider an orders table with columns for order keys, customer keys, product keys, order prices, and email addresses, with some containing arrays. A basic query in Vertica results in the following:

=> SELECT orderkey, custkey, prodkey, orderprices, email_addrs FROM orders LIMIT 5;
orderkey | custkey | prodkey | orderprices | email_addrs
------------+---------+-----------------------------------------------+-----------------------------------+----------------------------------------------------------------------------------------------------------------
113-341987 | 342799 | ["MG-7190 ","VA-4028 ","EH-1247 ","MS-7018 "] | ["60.00","67.00","22.00","14.99"] | ["bob@example,com","robert.jones@example.com"]
111-952000 | 342845 | ["ID-2586 ","IC-9010 ","MH-2401 ","JC-1905 "] | ["22.00","35.00",null,"12.00"] | ["br92@cs.example.edu"]
111-345634 | 342536 | ["RS-0731 ","SJ-2021 "] | ["50.00",null] | [null]
113-965086 | 342176 | ["GW-1808 "] | ["108.00"] | ["joe.smith@example.com"]
111-335121 | 342321 | ["TF-3556 "] | ["50.00"] | ["789123@example-isp.com","alexjohnson@example.com","monica@eng.example.com","sara@johnson.example.name",null]
(5 rows)
This example expands the orderprices column for a specified customer, in ascending order. The custkey and email_addrs columns are repeated for each array element.

=> SELECT EXPLODE(orderprices, custkey, email_addrs) OVER(PARTITION BEST) AS (position, orderprices, custkey, email_addrs)
FROM orders WHERE custkey='342845' ORDER BY orderprices;
position | orderprices | custkey | email_addrs
----------+-------------+---------+------------------------------
2 | | 342845 | ["br92@cs.example.edu",null]
3 | 12.00 | 342845 | ["br92@cs.example.edu",null]
0 | 22.00 | 342845 | ["br92@cs.example.edu",null]
1 | 35.00 | 342845 | ["br92@cs.example.edu",null]
(4 rows)

Pieter_Sheth-Vo

@SruthiA Thanks for such a timely reply!! Casting to set works perfectly.