How to read first element from an arraylist which is stored as varchar

karthigaimuthu · December 2021

['Baby Unisex', 'Baby']
['Pre-Owned']
['Mens']
['Boys', 'Mens']
['Accessories', 'Bags']
['Clothing', 'Unisex']
['Boys', 'Baby Unisex']
['Girls', 'Bags']
['Accessories', 'Mens']
['Clothing']
['Shoes']

mosheg · December 2021

Another example on v11.0.2

CREATE TABLE orders( orderid INT, shipments ARRAY[VARCHAR(100)]);

SET ESCAPE_STRING_WARNING TO OFF;
SET STANDARD_CONFORMING_STRINGS TO OFF;
COPY orders FROM STDIN DELIMITER '#' NULL '' COLLECTIONENCLOSE '\'' ABORT ON ERROR;
1#['Baby Unisex', 'Baby']
2#['Pre-Owned']
3#['Mens']
4#['Boys', 'Mens']
5#['Accessories', 'Bags']
6#['Clothing', 'Unisex']
7#['Boys', 'Baby Unisex']
8#['Girls', 'Bags']
9#['Accessories', 'Mens']
10#['Clothing']
11#['Shoes']
\.

SELECT VERSION();
               VERSION
-------------------------------------
 Vertica Analytic Database v11.0.2-0
(1 row)

SELECT * FROM orders;
 orderid |       shipments
---------+------------------------
       1 | ["Baby Unisex","Baby"]
       2 | ["Pre-Owned"]
       3 | ["Mens"]
       4 | ["Boys","Mens"]
       5 | ["Accessories","Bags"]
       6 | ["Clothing","Unisex"]
       7 | ["Boys","Baby Unisex"]
       8 | ["Girls","Bags"]
       9 | ["Accessories","Mens"]
      10 | ["Clothing"]
      11 | ["Shoes"]
(11 rows)

SELECT shipments[0] FROM orders WHERE orderid= 5;
  shipments
-------------
 Accessories
(1 row)

SELECT shipments[1] FROM orders WHERE orderid= 5;
 shipments
-----------
 Bags
(1 row)

Jim_Knicely · December 2021

@karthigaimuthu - The STRING_TO_ARRAY function was introduced in Vertica 9.3.1. But in Vertica 9.3.x you cannot define a Vertica managed table's column as an ARRAY, only an external table's column's data type. But starting in Vertica 10.0, you can define a managed table's column as an ARRAY!

marcothesane · December 2021

Does this server your purpose?

WITH indata(s) AS (
          SELECT '[''Baby Unisex'', ''Baby'']'
UNION ALL SELECT '[''Pre-Owned'']'
UNION ALL SELECT '[''Mens'']'
UNION ALL SELECT '[''Boys'', ''Mens'']'
UNION ALL SELECT '[''Accessories'', ''Bags'']'
UNION ALL SELECT '[''Clothing'', ''Unisex'']'
UNION ALL SELECT '[''Boys'', ''Baby Unisex'']'
UNION ALL SELECT '[''Girls'', ''Bags'']'
UNION ALL SELECT '[''Accessories'', ''Mens'']'
UNION ALL SELECT '[''Clothing'']'
UNION ALL SELECT '[''Shoes'']'
)
SELECT
  s
, (STRING_TO_ARRAY(s))[0] AS firstelement
FROM indata;
-- out             s            | firstelement  
-- out -------------------------+---------------
-- out  ['Baby Unisex', 'Baby'] | 'Baby Unisex'
-- out  ['Pre-Owned']           | 'Pre-Owned'
-- out  ['Mens']                | 'Mens'
-- out  ['Boys', 'Mens']        | 'Boys'
-- out  ['Accessories', 'Bags'] | 'Accessories'
-- out  ['Clothing', 'Unisex']  | 'Clothing'
-- out  ['Boys', 'Baby Unisex'] | 'Boys'
-- out  ['Girls', 'Bags']       | 'Girls'
-- out  ['Accessories', 'Mens'] | 'Accessories'
-- out  ['Clothing']            | 'Clothing'
-- out  ['Shoes']               | 'Shoes'

marcothesane · December 2021

Does this server your purpose?

WITH indata(s) AS (
          SELECT '[''Baby Unisex'', ''Baby'']'
UNION ALL SELECT '[''Pre-Owned'']'
UNION ALL SELECT '[''Mens'']'
UNION ALL SELECT '[''Boys'', ''Mens'']'
UNION ALL SELECT '[''Accessories'', ''Bags'']'
UNION ALL SELECT '[''Clothing'', ''Unisex'']'
UNION ALL SELECT '[''Boys'', ''Baby Unisex'']'
UNION ALL SELECT '[''Girls'', ''Bags'']'
UNION ALL SELECT '[''Accessories'', ''Mens'']'
UNION ALL SELECT '[''Clothing'']'
UNION ALL SELECT '[''Shoes'']'
)
SELECT
  s
, (STRING_TO_ARRAY(s))[0] AS firstelement
FROM indata;
-- out             s            | firstelement  
-- out -------------------------+---------------
-- out  ['Baby Unisex', 'Baby'] | 'Baby Unisex'
-- out  ['Pre-Owned']           | 'Pre-Owned'
-- out  ['Mens']                | 'Mens'
-- out  ['Boys', 'Mens']        | 'Boys'
-- out  ['Accessories', 'Bags'] | 'Accessories'
-- out  ['Clothing', 'Unisex']  | 'Clothing'
-- out  ['Boys', 'Baby Unisex'] | 'Boys'
-- out  ['Girls', 'Bags']       | 'Girls'
-- out  ['Accessories', 'Mens'] | 'Accessories'
-- out  ['Clothing']            | 'Clothing'
-- out  ['Shoes']               | 'Shoes'

karthigaimuthu · December 2021

Thanks @marcothesane for your quick turnaround. @mosheg has replied with desired result.

Thanks @mosheg it really helpful , but I unable to use it because we are using lower version of vertica we might have to upgrade it.

@mosheg , this function will work with vertica version v9.3.1-3/v10.0.1-2

How to read first element from an arraylist which is stored as varchar

Best Answers

Answers

Leave a Comment