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

JSON Column in Regular Table Not copying properly to Flex Table

Hi all,

I have a varchar column in one of my regular tables that has json data like so:
{"user"=>"x","newPortfolio"=>"false","tickers_added"=>"["BRK.A", "BA", "MORN", "VITSX"]","tickers_already_in_portfolio"=>"[]","rta"=>"true"}
I called this column "try".

I created a Flex Table (dwh.try_flex), and then copied "try" into the Flex Table:
INSERT INTO dwh.try_flex SELECT try FROM temp_table;

The problem I'm running into is that "try" is now my Key, and the values is the JSON string in the try column.

How can I parse the json from the try column into the flex table properly?

Comments

  • Bryan_HBryan_H Employee

    I will try to see if there is a better way than this, but you could use vsql to list the JSON and pipe to another vsql to COPY the JSON to flex, like this:

    Setup
    dbadmin=> create table try_src (txt long varchar);
    CREATE TABLE
    dbadmin=> create flex table try_flex();
    CREATE TABLE
    dbadmin=> insert into try_src values ('{"user":"x","newPortfolio":"false","tickers_added":["BRK.A", "BA", "MORN", "VITSX"],"tickers_already_in_portfolio":[],"rta":"true"}');

    OUTPUT

      1
    

    (1 row)

    dbadmin=> commit;
    COMMIT

    vsql SELECT pipe to vsql COPY here:
    vsql -U dbadmin -w XXX -A -t -c "select txt from try_src;" | vsql -U dbadmin -w XXX -c "copy try_flex from stdin parser fjsonparser();"

    Verify
    dbadmin=> select maptostring(raw) from try_flex;

    maptostring

    {
    "newPortfolio": "false",
    "rta": "true",
    "tickers_added": {
    "0": "BRK.A",
    "1": "BA",
    "2": "MORN",
    "3": "VITSX"
    },
    "tickers_already_in_portfolio": {},
    "user": "x"
    }
    (1 row)

  • twalltwall Employee

    Flex tables use a custom encoding that differs from JSON. If you insert JSON the way you've done it will be interpreted as a plain old string value. Instead, you need to convert your 'try' column into the flex VMap format by doing something like:

    INSERT INTO dwh.try_flex SELECT MapJSONExtractor(try) FROM temp_table

    FJSONParser does a similar thing, but it is a parser, so it can only run in the context of a COPY statement. If you already have JSON data in a column of another table MapJSONExtractor can convert it to the flex VMap format without having to reload the data.

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.