group by JSON Column that contains an array

I have the following table:

that contains these data:

How can I group by my array items in my JSON Column? and get this result:

Answer

You can try to use OPENJSON with CROSS APPLY to make it.

SELECT
    col1,
     UserID
FROM T t1
CROSS APPLY
    OPENJSON(t1.Roles)
    WITH
        (
            col1 varchar(50) N'$'
        ) AS a

sqlfiddle