CODEWITHSUNDEEP
mysqlsqljsonselectgroup-by
Nicolas correa
Nicolas correa

Reputation: 31

how can i transform a mysql query "group by statement" result to a json structure?

i have a query with a group by statement and i want to transform that data in a proper json object

query:

SELECT c.name, count(u.id) 
FROM users AS u 
INNER JOIN contries AS c ON u.country_id = c.id 
GROUP BY c.id

expected result:

{canada: {count: 1565}, colombia: {count:15645612}}

Upvotes: 2

Views: 731

Answers (3)

Med Elgarnaoui
Med Elgarnaoui

Reputation: 1667

"select return JSON", there are two ways of doing this :

  1. use a "hack" - see the db-fiddle here,
  2. or use one of the new MySQL supplied JSON functions here - which, ironically, appears to be even more of a hack than the hack itself! Only with MySQL! : (fiddle here).

Upvotes: 0

JCT
JCT

Reputation: 1

SELECT JSON_OBJECT(
  'name', c.name,
  'count', count(u.id)
)
FROM users as u INNER JOIN contries AS c ON u.country_id = c.id 
GROUP BY c.id;

Upvotes: 0

GMB
GMB

Reputation: 222512

You can add a level of aggregation and use json_objectagg() (available starting MySQL 5.7):

select json_objectagg(name, js_count) js
from (
    select c.name, json_object('count', count(u.id)) js_count
    from users as u 
    inner join contries as c on u.country_id = c.id 
    group by c.id, c.name
) t

Upvotes: 2

Related Questions