How to create JSON format with group by and count functions in mysql?

user_5730893 0
Viewed 191

I need to get count of group by key as an array of json.

table:

designation      | name  
------------------------
Engineer         | Ben      
Sr.Engineer      | Tom      
Lead Engineer    | Dan
Engineer         | Michle

output that I need:

grouped_data

[{designation:"Engineer",count:2},{designation:"Sr.Engineer",count:1}, {designation:"Lead Engineer",count:1}]

I tried 

SELECT JSON_ARRAYAGG(JSON_OBJECT('designation', designation,  'total_users', count(*) as total_users)) as user_spit_up
FROM user group by designation

But its getting error

Invalid use of group function.

Help please. Thanks in advance,

count group-by mysql
1 Answers

I had the same problem and this was the solution that worked for me. For your problem it would be something like this:

SELECT JSON_ARRAYAGG(a.array_counts)
FROM (SELECT JSON_OBJECT(
                     'designation', designation,
                     'total_users', COUNT(designation)
                 ) array_counts
      FROM user
      GROUP BY designation
     ) a
user_167172110
Related