Extract JSON Data From Regular Table

bmurrellbmurrell Community Edition User

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.




  • bmurrellbmurrell Community Edition User

    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 : -

    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.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file