JSON datatype like MSSQL Server?

Moderator: NorbertKrupa

Post Reply
User avatar
Josh
Intermediate
Intermediate
Posts: 106
Joined: Thu Jan 26, 2012 9:38 pm

JSON datatype like MSSQL Server?

Post by Josh » 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?
Thank you!
Joshua

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1823
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: JSON datatype like MSSQL Server?

Post by JimKnicely » 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)
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

scutter
Master
Master
Posts: 302
Joined: Tue Aug 07, 2012 2:15 am

Re: JSON datatype like MSSQL Server?

Post by scutter » 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
Sharon Cutter
Vertica Consultant, Zazz Technologies LLC

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1823
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: JSON datatype like MSSQL Server?

Post by JimKnicely » 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. :oops: I screwed up the CREATE TABLE command from the beginning... Duh!
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “New to Vertica”