Convert ARRAY to SET?

edited November 2022 in General Discussion

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

Answers

  • SruthiASruthiA Vertica Employee Administrator

    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.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file