Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.