JSON Column in Regular Table Not copying properly to Flex Table

mc123

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?

Bryan_H

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)

twall

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.