Trying to convert STRING column into Array of Structs in SQL statement

Data Engineering

Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.

I have STRING column in a DLT table that was loaded using SQL Autoloader via a JSON file. When I use the "schema_of_json" function in a SQL statement passing in the literal string from the STRING column then I get this output:

ARRAY<STRUCT<firstFetchDate: STRING, lastFetchDate: STRING, matchedObjectArray: ARRAY<STRUCT<highlightSnippet: STRING, matchedObjectAPI: STRING, matchedObjectAttribute: STRING, matchedObjectId: STRING, matchedObjectName: STRING, object: STRING>>, name: STRING, normDetailsAPI: STRING, normId: STRING, object: STRING>>

I am trying to figure out how to get this STRING datatype into a more structure format using a SQL statement.

Thanks for the help.