Extract JSON Data From Regular Table

bmurrell

Hello,
I'm loading some JSON into a staging flex table and extracting various keys. I'm then inserting that into a regular table. No problem there.
Then I've found some extra keys I'd like to extract. I can quickly change the SQL to load those into the regular table.
However, to future proof this I'm loading the raw column into the regular table too (column called rawjson), as a LONG VARBINARY the same as the flex table.
How do I extract a key from the regular tables rawjson field ?
It appears to want a varchar, not a long varbinary.

Thanks

bmurrell

Ok, I've fixed this.
I created the rawjson column in the real table as "varchar(32000)", and when inserting into the real table from the flex table, I use the following : -
CAST(MAPTOSTRING(raw) AS VARCHAR)

Then if I need to extract a new field in the real table : -

SELECT maplookup(mapjsonextractor(rawjson),'SomeNewField')

I could add a new column to the table with this as a default if I needed to.