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:
[{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,
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