How to return an array from a SQL subquery?

Question

I'm trying to query for a list of person's names and all their friends from my database. I need their list of friends to be in an array adjacent to their row for formatting purposes.

Here is my query:

$sql = "SELECT CONCAT(r.first_name, ' ', r.last_name) as name, 
          ( SELECT CONCAT(first_name, ' ', last_name) as friend_name 
                FROM friend_list WHERE friend = r.id) as friends 
        FROM friend_list r";

Because people have multiple friends, I need this to return an array of friends for each person. how would I do this?

MySQL has no datatype called "array", so you cannot do this. You can, however, format the data as a string or return JSON, and then parse out an array. — Gordon Linoff, Jun 10 '19 at 16:37

Augusto Moura · Accepted Answer · 2019-06-10T17:22:20.513

(Expanding on @GordonLinoff 's comment)

There is no way to directly retrieve an array. What you can do is generate a "serialized" array inside the query using GROUP_CONCAT, and then unserialize it on your application.

--EDIT-- Upon further testing, I've noticed the below code doesn't work as intended. But the idea of serializing and then unserializing still stands.

$sql = " SELECT CONCAT(r.first_name, ' ', r.last_name) as name, GROUP_CONCAT ( CONCAT(f.first_name, ' ', f.last_name) SEPARATOR '|' ) as friends FROM friend_list r JOIN friend_list f ON f.friend = r.id;" ;

And to "unserialize" it in PHP:

$friendsArray = explode('|', $r['friends'])

You are missing the GROUP BY clause. – Paul Spiegel Jun 10 '19 at 17:34 — Paul Spiegel, Jun 10 '19 at 17:34

score 1 · Answer 2 · answered Jun 10 '19 at 16:43

I answered a similar one to this before using the below script. The logic will be the same just change the table/field names to fit your one;

DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',MAX(CASE WHEN UserName = ''' 
                    + p.UserName + ''' THEN FriendName END) AS ' 
                    + QUOTENAME(p.UserName) FROM Popular p
            FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')

SET @query = 'SELECT ' + @cols + ' FROM 
            (SELECT UserName, FriendName
                ,ROW_NUMBER() OVER (PARTITION BY UserName ORDER BY FriendName) AS RowNum
                FROM Popular GROUP BY USERNAME, FRIENDNAME
            ) x
            GROUP BY RowNum'

EXECUTE(@query);

It will result in columns of users with their friends in new rows as per this;

╔════════╦══════╦════════╗
║  John  ║ Khan ║ Philip ║
╠════════╬══════╬════════╣
║ Jeremy ║ Lemy ║ Brock  ║
║ Marry  ║ NULL ║ Ursula ║
║ Sarah  ║ NULL ║ NULL   ║
╚════════╩══════╩════════╝

original thread can be found here; SQL - Union All Users in One Table

(1) This answer is for SQL Server and not MySQL. (2) If you are going to just repeat an answer, just close as a duplicate. (3) If you want to help the user, then adjust the answer for his/her question. — Gordon Linoff, Jun 10 '19 at 16:59

How to return an array from a SQL subquery?

2 Answers2