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

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?


  • Options
    Hi James,

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


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

    Does that help?

  • Options

    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:





    => 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].

    "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?

  • Options



    >> 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.


  • Options

    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."




Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file