SSRS - extract data from column containing JSON

Question

I have a dataset with a column containing arrays of JSON data that looks like:

[{"name":"aaa","type":"yyy"},{"name":"bbb","type":"ccc"}]

or more specifically:

Is there any straight forward method of extracting the JSON data from the column using something like JSON_QUERY, so that I can use it in a report

Take a look at this. You can convert the json into a table format then use it in as you would a normal table. docs.microsoft.com/en-us/sql/relational-databases/json/… — Alan Schofield

Mike Mike · Accepted Answer · 2020-02-18T20:58:20

As far as I can tell, the existing JSON array format is not usable with any of the T-SQL JSON functions.

The array in the column "jsonCol" needs to be in the form of:

{ "tag": [{"name":"aaa","type":"yyy"},{"name":"bbb","type":"ccc"}]}

and then I can extract each array element individually with:

SELECT JSON_QUERY(jsonCol, '$.tag[0]') as tag FROM

So I could add a prefix and suffix string to the select statement to fix this as long as no one else will see it.