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?
Tagged:
0
Leave a Comment
Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.
Comments
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:
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.