group by JSON Column that contains an array

user_4107888 0
Viewed 920

I have the following table:

Table

that contains these data:

Data

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

Expected Result

group-by sql-server-2016 sql-server sql
2 Answers

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

user_51760710

If you cannot use OPENJSON, here is an alternative

Select
    aRole, COUNT(*) as cnt
From (

    Select
        a.ID
        ,b.Items as aRole
    From 
        (Select *
            ,replace(replace(replace(Roles,'[',''),']',''),'"','') as Rolesx
         From JSONgroup) a
    Cross Apply dbo.Split(a.Rolesx, ',') b
) c
 group by aRole
user_31433390
Related