Convert multiple MYSQL rows to only one array column [duplicate]

Question

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

I'm confused why you would do this. Why not just SELECT name FROM user WHERE country_id = <whatever> and then fetchAll() or equivalent? — Alex Howansky
– Alex Howansky, Commented Jul 19, 2024 at 19:19
@AlexHowansky, agreed, but for this very particular case I need this specific structure (1:n) — Jeú Casulo
– Jeú Casulo, Commented Jul 19, 2024 at 20:15
Topical content: How to create a three dimensional array from sql query PHP and How do I retrieve results as multidimensional array from mySQL and PHP? Return MySQL query as multidimensional array and How to populate PHP 3D array from MySQL and How to make multidimensional array from mysql — mickmackusa
– mickmackusa ♦, Commented Jul 20, 2024 at 2:15

Slava Rozhnev · Accepted Answer · 2024-07-19 20:26:32Z

2

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

answered Jul 19, 2024 at 20:26

Slava Rozhnev

10.2k6 gold badges27 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Manuel Guzman Over a year ago

Does it handle the conversion to php array? If not, only needs to add $var = json_decode($query['users], true)

mickmackusa Over a year ago

Is this a new insight on Stack Overflow or is this possibly a duplicate question?

Jeú Casulo Over a year ago

thanks, it worked. It was not working before because of my apache/mysql version. But now it is working. Sorry for my delay, i lost my computers the last days. Thanks

Collectives™ on Stack Overflow

Convert multiple MYSQL rows to only one array column [duplicate]

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related