The Vertica Database Forums

Posted: **Fri Mar 04, 2016 2:42 pm**

Does Vertica have a table column data type JSON like MS SQL Server? If not, are there any recommendations on how I can store JSON data in a VARCHAR column, but have the ability to extract elements from the data?

Posted: **Fri Mar 04, 2016 3:28 pm**

Hi,

Vertica does not support JSON data types directly. However, we do have Flex tables where you can extract data attributes.

Example:

Code: Select all

dbadmin=> CREATE TABLE json_test (custom_data VARCHAR(65000), custom_data_mapped LONG VARBINARY);
CREATE TABLE

dbadmin=> COPY json_test (custom_data, custom_data_mapped AS MAPJSONEXTRACTOR(custom_data USING PARAMETERS flatten_maps=false, flatten_arrays=false)) FROM STDIN;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> {"employees":[ {"firstName":"Daryl", "lastName":"Dixon"}, {"firstName":"Rick", "lastName":"Grimes"}, {"firstName":"Glenn", "lastName":"Rhee"} ]}
>> \.

Code: Select all

dbadmin=> SELECT
dbadmin->    MAPLOOKUP(
dbadmin(>       MAPLOOKUP(
dbadmin(>          MAPLOOKUP(custom_data_mapped, 'employees'),
dbadmin(>       '0'),
dbadmin(>    'firstName')
dbadmin-> FROM json_test;
 MAPLOOKUP
-----------
 Daryl
(1 row)

Code: Select all

dbadmin=>
dbadmin=> SELECT
dbadmin->    MAPLOOKUP(
dbadmin(>       MAPLOOKUP(
dbadmin(>          MAPLOOKUP(custom_data_mapped, 'employees'),
dbadmin(>       '1'),
dbadmin(>    'firstName')
dbadmin-> FROM json_test;
 MAPLOOKUP
-----------
 Rick
(1 row)

Code: Select all

dbadmin=>
dbadmin=> SELECT
dbadmin->    MAPLOOKUP(
dbadmin(>       MAPLOOKUP(
dbadmin(>          MAPLOOKUP(custom_data_mapped, 'employees'),
dbadmin(>       '2'),
dbadmin(>    'firstName')
dbadmin-> FROM json_test;
 MAPLOOKUP
-----------
 Glenn
(1 row)

Posted: **Mon Mar 07, 2016 1:52 pm**

One clarification on the previous reply. That example isn’t using a flex table - it’s a CREATE TABLE rather than CREATE FLEX TABLE. It’s creating a vmap for the json, and using the MAP functions to access it.

Creating a flex table gives you more transparent mechanisms for accessing the json keys as columns - you get a view that transposes the keys into columns.

When you store the vmap in the column, you don’t get the keys as column names, but you can still use the MAP functions to pull out the values.

See the documentation for examples of both:

https://my.vertica.com/docs/7.2.x/HTML/ ... nextractor

—Sharon

Posted: **Tue Mar 08, 2016 10:22 pm**

Thanks, Sharon! You know what's weird is that the whole time I was testing this I thought I was using a flex table.

I screwed up the CREATE TABLE command from the beginning... Duh!

The Vertica Database Forums

JSON datatype like MSSQL Server?

JSON datatype like MSSQL Server?

Re: JSON datatype like MSSQL Server?

Re: JSON datatype like MSSQL Server?

Re: JSON datatype like MSSQL Server?