Unable to find the referenced field using Convert_fromJSON on first try

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.

Using Dremio v25, the outer query can find the referenced field:

I still get an error Unable to find the referenced field: [replacement0.dataField].

Are you sure this was the first time you ever ran a convert off that table?