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.
Best Answer
-
Options
Jim_Knicely
- Select Field - Administrator
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)0
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.