title: How to create JSON format with group by and count functions in mysql?
body: <p>I need to get count of group by key as an array of json. </p>
  
  <p><strong>table:</strong></p>
  
  <pre><code>designation      | name  
  ------------------------
  Engineer         | Ben      
  Sr.Engineer      | Tom      
  Lead Engineer    | Dan
  Engineer         | Michle  
  </code></pre>
  
  <p><strong>output that I need:</strong></p>
  
  <h2>grouped_data</h2>
  
  <pre><code>[{designation:"Engineer",count:2},{designation:"Sr.Engineer",count:1}, {designation:"Lead Engineer",count:1}]
  </code></pre>
  
  <p>I tried </p>
  
  <pre><code>SELECT JSON_ARRAYAGG(JSON_OBJECT('designation', designation,  'total_users', count(*) as total_users)) as user_spit_up
  FROM user group by designation
  </code></pre>
  
  <p>But its getting error</p>
  
  <blockquote>
    <p>Invalid use of group function.</p>
  </blockquote>
  
  <p>Help please.
  Thanks in advance,</p>