Retrieving data from a JSON array with SQL Simon Hutchinson

​I have noticed with a few of the enhancements made in recent Technology Refreshes to various SQL Views and Table functions that new columns have been added that contain JSON arrays. For example the BASED_ON_FILES column in the SYSFILES View. I want to be able to convert the data held within the JSON array into individually columns. With Join logical files, SQL Views and Indexes the object could be based upon more than one data source.
Before I show how to extract the data from the BASED_ON_FILES columns I want to give a simpler example that will allow me to explain how to do it. Before I start I need to thank Brian Hill for his help with the code.
My example JSON array looks like:
Read more » Read More 

Verified by MonsterInsights