JSON datatype like MSSQL Server?
Moderator: NorbertKrupa
JSON datatype like MSSQL Server?
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
Joshua
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: JSON datatype like MSSQL Server?
Hi,
Vertica does not support JSON data types directly. However, we do have Flex tables where you can extract data attributes.
Example:
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
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: JSON datatype like MSSQL Server?
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
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
Vertica Consultant, Zazz Technologies LLC
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: JSON datatype like MSSQL Server?
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!
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.