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


What will be format of nested array data in csv — Vertica Forum

What will be format of nested array data in csv

When I'm going to insert data like this [[1,2,3]] getting error, so what could be nested array data structures in csv

Answers

  • moshegmosheg Vertica Employee Administrator

    Here is an example of how to load a nested array in INSERT and via bulk load using a COPY statement.

    CREATE TABLE array_test
    (
        f1 int,
        f2 ARRAY[VARCHAR(80)],
        f3 ARRAY[ARRAY[INT]]
    );
    INSERT INTO array_test VALUES (1, ARRAY['a', 'b', 'c'], ARRAY[ARRAY[1,2,3]]);
    INSERT INTO array_test VALUES (2, '["d", "e", "f"]', ARRAY[[4,5,6]]);
    
    COPY array_test FROM STDIN PARSER fjsonparser();
    {"f1":3,"f2":["g","h","i"],"f3":[[7,8,9]]}
    {"f1":4,"f2":["j","k","l"],"f3":[[10,11,12]]}
    \.
    
    SELECT * FROM array_test order by 1;
    --  f1 |      f2       |      f3
    -- ----+---------------+--------------
    --   1 | ["a","b","c"] | [[1,2,3]]
    --   2 | ["d","e","f"] | [[4,5,6]]
    --   3 | ["g","h","i"] | [[7,8,9]]
    --   4 | ["j","k","l"] | [[10,11,12]]
    -- (4 rows)
    

Leave a Comment

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