how to create an array in vertica ??

ashu123ashu123 Registered User

hello,
I need to query on arrays,how to create an array , and how to query on arrays ?

Comments

  • ashu123ashu123 Registered User


    i tried but im getting like this ):

  • ashu123ashu123 Registered User

    plzz provide solution :(

  • Jim_KnicelyJim_Knicely Employee, Registered User, VerticaExpert
    edited September 24

    Hi,

    From your first post, here is a simple solution to get all the rows where the list contains a 2:

    dbadmin=> select * from dmp;
      name   |  id  |   list
    ---------+------+-----------
     asha    | 2725 | [1,2,3,4]
     vertica |   27 | [1,3,4]
     jim     |   43 | [2]
    (3 rows)
    
    dbadmin=> select * from dmp where list ilike '%2,%' or list ilike '%2]%';
     name |  id  |   list
    ------+------+-----------
     asha | 2725 | [1,2,3,4]
     jim  |   43 | [2]
    (2 rows)
    
  • ashu123ashu123 Registered User

    hii jim
    can you please explain that query to me , is ( , ) taking as % ??

  • ashu123ashu123 Registered User

    thanku :)

  • Jim_KnicelyJim_Knicely Employee, Registered User, VerticaExpert

    The data in the LIST column of your DMP table in your example appears to be VARCHAR. I simply retrieved the rows where the string value of the column matches a specified pattern.

    See: https://my.vertica.com/docs/8.1.x/HTML/index.htm#Authoring/SQLReferenceManual/LanguageElements/Predicates/LIKE-predicate.htm

    From the documentation notice that you can specify what strings to match. This pattern to match can include:

    • Underscore (_) matches any single character.
    • Percent sign (%) matches any string of zero or more characters.
  • ashu123ashu123 Registered User

    hii jim,
    How do we store array of integers in vertica (not flex table) not in string form because for string form ilike operation working but it takes too much time.So if it possible to store array of integers please explain me with an example.
    In the above example make that 'list' field as array

  • Jim_KnicelyJim_Knicely Employee, Registered User, VerticaExpert
    edited September 26

    Hi,

    There is no array data type for a column in a standard table in Vertica.

    Is the max size of the array known? You could create a separate INT column for each array element if you want to work with integers...

    One Example:

    dbadmin=> create table dmp (
    dbadmin(>   name varchar(10),
    dbadmin(>   id int,
    dbadmin(>   list varchar(100),
    dbadmin(>   list1 int default decode(split_part(translate(list, '[]', ''), ',', 1), '', null, split_part(translate(list, '[]', ''), ',', 1))::int,
    dbadmin(>   list2 int default decode(split_part(translate(list, '[]', ''), ',', 2), '', null, split_part(translate(list, '[]', ''), ',', 2))::int,
    dbadmin(>   list3 int default decode(split_part(translate(list, '[]', ''), ',', 3), '', null, split_part(translate(list, '[]', ''), ',', 3))::int,
    dbadmin(>   list4 int default decode(split_part(translate(list, '[]', ''), ',', 4), '', null, split_part(translate(list, '[]', ''), ',', 4))::int
    dbadmin(> );
    CREATE TABLE
    dbadmin=> insert into dmp (name, id, list) select 'asha', 2725, '[1,2,3,4]';
     OUTPUT
    --------
          1
    (1 row)
    
    dbadmin=> insert into dmp (name, id, list) select 'vertica', 27, '[1,3,4]';
     OUTPUT
    --------
          1
    (1 row)
    
    dbadmin=> insert into dmp (name, id, list) select 'jim', 43, '[2]';
     OUTPUT
    --------
          1
    (1 row)
    
    dbadmin=> select * from dmp;
      name   |  id  |   list    | list1 | list2 | list3 | list4
    ---------+------+-----------+-------+-------+-------+-------
     asha    | 2725 | [1,2,3,4] |     1 |     2 |     3 |     4
     vertica |   27 | [1,3,4]   |     1 |     3 |     4 |
     jim     |   43 | [2]       |     2 |       |       |
    (3 rows)
    
    dbadmin=> select * from dmp where list1 = 2 or list2 = 2 or list3 = 2 or list4 = 2;
     name |  id  |   list    | list1 | list2 | list3 | list4
    ------+------+-----------+-------+-------+-------+-------
     asha | 2725 | [1,2,3,4] |     1 |     2 |     3 |     4
     jim  |   43 | [2]       |     2 |       |       |
    (2 rows)
    

    Note that you can use FLEX table to flatten a JSON array out into unique columns.

    See:

    https://my.vertica.com/docs/8.1.x/HTML/index.htm#Authoring/FlexTables/FJSONPARSERreference.htm?Highlight=flatten_arrays

  • Jim_KnicelyJim_Knicely Employee, Registered User, VerticaExpert

    You can also create a view...

    dbadmin=> select * from dmp;
      name   |  id  |   list
    ---------+------+-----------
     asha    | 2725 | [1,2,3,4]
     jim     |   43 | [2]
     vertica |   27 | [1,3,4]
    (3 rows)
    
    dbadmin=> create view dmp_view as
    dbadmin-> select
    dbadmin->   name,
    dbadmin->   id,
    dbadmin->   list,
    dbadmin->   decode(split_part(translate(list, '[]', ''), ',', 1), '', null, split_part(translate(list, '[]', ''), ',', 1))::int list1,
    dbadmin->   decode(split_part(translate(list, '[]', ''), ',', 2), '', null, split_part(translate(list, '[]', ''), ',', 2))::int list2,
    dbadmin->   decode(split_part(translate(list, '[]', ''), ',', 3), '', null, split_part(translate(list, '[]', ''), ',', 3))::int list3,
    dbadmin->   decode(split_part(translate(list, '[]', ''), ',', 4), '', null, split_part(translate(list, '[]', ''), ',', 4))::int list4
    dbadmin-> from dmp;
    CREATE VIEW
    
    dbadmin=> select * from dmp_view where list1 = 2 or list2 = 2 or list3 = 2 or list4 = 2;
     name |  id  |   list    | list1 | list2 | list3 | list4
    ------+------+-----------+-------+-------+-------+-------
     asha | 2725 | [1,2,3,4] |     1 |     2 |     3 |     4
     jim  |   43 | [2]       |     2 |       |       |
    (2 rows)
    

Leave a Comment

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