Mysql Question posted in
The official MySQL documentation can be found here.

Convert multiple MYSQL rows to only one array column

I have a simple database with the user and the country tables.
Which user belongs to only one country.
Which country has a list of users.

Right now Im using group_concat (code below) and its working in a "manual" way where Im suppose to build the array myself in PHP.

That’s my output right now:

country userFromCountry
Brazil John / Richard / Robert

But I wonder if there’s a better way to bring the data as an "automatic" array, something like that

country userFromCountry
Brazil [John] [Richard] [Robert]

I could make it "manually" with concat but when I try with JSON_ARRAY i have a "more than one row returned" error.

Fiddle example:
https://sqlfiddle.com/mysql/online-compiler?id=a2bede84-5293-4366-aa84-ff285322b92a

Thank you

Tags:

2

Answers


  1. 0 
    SELECT country.name as country,
  JSON_ARRAYAGG(user.name) AS userFromCountry
FROM country
JOIN user ON user.country_id = country.ID
GROUP BY country;

    JSON_ARRAYAGG() is available in MySQL 5.7.22 and later.

    Login or Signup to reply.
  2. 0

    You just need to use JSONARRAY_AGG aggregation function. Also using JOIN the tables is more efficient way to achieve desired result, so I can advise next query:

    SELECT country.name as country, JSON_ARRAYAGG(user.name) AS userFromCountry
FROM `country`
JOIN `user` ON user.country_id=country.ID
GROUP BY country.name;

    Try the SQL code here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search