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?

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 Employee

    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