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?

Comments

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

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

     

  • 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

Leave a Comment

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