I have some fields stored in a database as a json formatted VARCHAR.
I have an issue where if I use CONVERT_FROMJSON on the field, and then try to reference a field within the json, it gives me an error “Unable to find the referenced field”
Example: Select data[‘nestedData’] from (select convert_fromjson(dataField) as data from table)
If I run “(select convert_fromjson(dataField) as data from table” then run the above query, it works fine, but it will fail every time until I run a query that converts the field first.
I have tried rewriting it with a “With” clause and that didn’t work either.
The only thing that did work was to create a virtual dataset that runs converts the field to json, then running the query against that. But that won’t be feasible to do for every scenario we need this for.
