The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!
Convert ARRAY to SET?
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.
0
Answers
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","[email protected]"]
111-952000 | 342845 | ["ID-2586 ","IC-9010 ","MH-2401 ","JC-1905 "] | ["22.00","35.00",null,"12.00"] | ["[email protected]"]
111-345634 | 342536 | ["RS-0731 ","SJ-2021 "] | ["50.00",null] | [null]
113-965086 | 342176 | ["GW-1808 "] | ["108.00"] | ["[email protected]"]
111-335121 | 342321 | ["TF-3556 "] | ["50.00"] | ["[email protected]","[email protected]","[email protected]","[email protected]",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 | ["[email protected]",null]
3 | 12.00 | 342845 | ["[email protected]",null]
0 | 22.00 | 342845 | ["[email protected]",null]
1 | 35.00 | 342845 | ["[email protected]",null]
(4 rows)
@SruthiA Thanks for such a timely reply!! Casting to set works perfectly.