Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

How to load JSON array of primitives into column with array type

I have JSON to load, where one field is an array of float:
{...., "exp" : [ 2.3, 4.5, .... ], ...}
I am loading it with flattening array in FJSONPARSER, and having column names exp_0, exp_1,...
It works.
Now, Vertica released array support as column data type, I got an idea to use shiny new functionality and convert many exp_X columns into one with type ARRAY[float]. I am on 10.0, cannot use array size limiter.
I created table with column type ARRAY, started loader, hold my breath, and.... guess it does not work. FJSONPARSER does not understand input field with array of primitives to be loaded into array column.

I have slight hope that there is a magic parameter to FJOSNPARSER that can be set and it will load... please tell me magic happens.

Though, being realist, I understand that it is not supported (yet).

Question here: 3 years ago Vertica published source of flex UDx, including FJSONPARSER, on github.
Can you publish FJOSNPARSER code from 10.0.

I will add support for loading JSON field with array of primitives into field with type array of primitives, and will publish it back.
I believe I can make usable code, though Vertica will need to add unit tests.
Please talk to Chuck.

Tagged:

Best Answer

  • Jim_KnicelyJim_Knicely Administrator
    edited April 14 Accepted Answer

    The FJSONPARSER in Vertica 10.1 supports primitive arrays...

    Example:

    verticademos=> SELECT version();
                   version
    -------------------------------------
     Vertica Analytic Database v10.1.0-3
    (1 row)
    
    verticademos=> \! cat /home/dbadmin/example.json
    {"number":123, "street":"301 Grant","attributes":[1, 2, 3, 4]}
    
    verticademos=> CREATE TABLE customer(number INT, street VARCHAR, attributes ARRAY[INT]);
    CREATE TABLE
    
    verticademos=> COPY customer FROM '/home/dbadmin/example.json' PARSER fjsonparser();
     Rows Loaded
    -------------
               1
    (1 row)
    
    verticademos=> SELECT * FROM customer;
     number |  street   | attributes
    --------+-----------+------------
        123 | 301 Grant | [1,2,3,4]
    (1 row)
    

Answers

  • yes... glad to hear it is supported in 10.1.
    Very good reason not to skip 10.1. Plan is to start rolling 10.1 after patchset will be released.
    Thanks for quick answer.

Leave a Comment

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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.