Convert ARRAY to SET?

Answers

• Options

  SruthiA Vertica Employee Administrator

  November 2022

  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)

  1
• Options

  Pieter_Sheth-Vo ✭✭

  November 2022

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

  0