Search code examples
mysqlgroup-concat

query returning multidimensional grouped arrays

In the hypothetical scenario where I have a couple of database tables, in a one to many relationship. I am trying to find a nice query function so that I can return each row in table A with a nested array for all the linked rows from table B.

SELECT a.id AS id,a.name as name,b.id AS b_id,b.name AS b_name FROM a,b WHERE a.id=b.eid;

will return only one result

SELECT 
    a.id AS id,
    a.name as name,
    GROUP_CONCAT(b.id) AS b_id,
    GROUP_CONCAT(b.name) AS b_name 
FROM a,b 
WHERE a.id = b.eid;

whereas this returns what I want but I'm after an array not a string (there might be commas in b.name)

What am I missing?

Solution

  • You can use separator different from comma: 

    GROUP_CONCAT(b.name SEPARATOR ';') AS b_name