Skip to main content
Question

Extract JSON Data From Regular Table

  • October 17, 2023
  • 1 reply
  • 3 views
bmurrell
Forum|alt.badge.img+2

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

    1 reply

    bmurrell
    Forum|alt.badge.img+2
    • bmurrell
    • Author
    • Participating Frequently
    • October 17, 2023

    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.

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement