Create flex table from existing table or accessing JSON in non-flex table

James_Pettyjohn

I have tables I'm importing from another database which have JSON a few encoded fields . Is there a way to create a flex table based on a column of an existing table or a way to parse the contents of a single cell?

[Deleted User]

Hi James,

If you have access to Vertica 7.1, take a look at the new function "MapJSONExtractor()":

http://my.vertica.com/docs/7.1.x/PDF/HP_Vertica_7.1.x_Flex_Tables.pdf

(See page 70.)  This function parses a JSON string into a Vertica map structure, which is what Flex uses internally.

Does that help?

Adam

d23

mapjsonextractor works fine, for single objects. 

 

However if i create a table "jsondata" with a single varchar column called "json" and add 2 rows with the following data:

 

[{"el":1,"k1":"v1","k2":"v2"}]

[{"el":2,"k1":"v1","k2":"v2"},{"el":3,"k1":"v1","k2":"v2"}]

 

=> select maptostring(mapjsonextractor(json)) from jsondata;

WARNING 3224: Error in calling destroy() for User Defined Scalar Function MapJSONExtractor at [src/JSONParser.cpp:902], error code: 0, message: Rejecting row [4].

maptostring
----------------------------------------------------
{
"el" : "1",
"k1" : "v1",
"k2" : "v2"
}

(2 rows)

 

If i load the same data into a flex table it does work properly, however i do not know of a way that i can query a varchar column with json data of an existing table (which is what i have to work with) and insert it into a flex table.

 

Any suggestions on how to properly work with data like this?

sKwa

Hi!

 

>> Error in calling destroy() for User Defined Scalar

 

Types of UDxs

 

• User Defined Scalar Functions (UDSFs) take in a single row of data and return a single value. These functions can be used anywhere a native HP Vertica function can be used, except CREATE TABLE BY PARTITION and SEGMENTED BY expressions.

[...]

 

Means that UDSF returns one row per one value and your single value contains 2 rows:

1. {"el":2,"k1":"v1","k2":"v2"}
2. {"el":3,"k1":"v1","k2":"v2"}

 

>> Any suggestions on how to properly work with data like this?

Split data: single VARCHAR = single JSON row.

 

d23

That explains things.

 

From the documentation however i was under the impression that this would be a UDTF: 

 

"Extracts content of repeated JSON data objects, including nested maps, or data with an outer list of JSON elements."

 

Cheers,

Dennis