We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


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

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

karthigaimuthukarthigaimuthu
in General Discussion

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

Tagged:

Best Answers

  • moshegmosheg Vertica Employee Administrator
    Answer ✓

    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_KnicelyJim_Knicely - Select Field - Administrator
    Answer ✓

    @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!

Answers

  • marcothesanemarcothesane - Select Field - Administrator

    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'
  • marcothesanemarcothesane - Select Field - Administrator

    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'
  • karthigaimuthukarthigaimuthu

    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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Format
SpoilerCodeQuote
Heading 2Heading 1
Emoji
:):D:(;):/:o:s:p:'(:|B):#:*<3o:)>:)
Url
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file
Home • General DiscussionComment As ...