So your table is storing the JSON in some form of character column. Got it.

You cannot DIRECTLY do what you want. You cannot write a SELECT statement against JSON data in SQL Server. You need to first use some mechanism to PARSE your JSON into some type of tabular format (I recommend SQL CLR for that personally). You could write TSQL to do the parsing but TSQL is not very good at that (if you pursue it, look up SUBSTRING and CHARINDEX). You can also likely (not certain) do some XML stuff to work the parsing too. I foresee numerous complexities (and thus time involvement) in creating rigorous parsing code here, so I consider this beyond the realm of a forum post for assistance. Perhaps someone else will take up the charge here.

Best of luck!