SQL Concat Subquery

Question

I would like the "user_names" to be a list of users, but I can't get multiple subquery items to be assigned to "user_names". It work's if the subquery only returns 1 item, but doesn't work if it returns multiple.

Bed Table
[id]
[1]
[2]
[3]

Assignment Table:
[id, bed_id, user_id]
[1, 1, 1]
[2, 1, 2]

Users Table:
[id, 'user_name']
[1, 'John Smith']
[2, 'Jane Doe']

    sql = "SELECT
        b.id,
        (
            SELECT
                u.user_name
            FROM
                assignments AS a
            INNER JOIN
                users as u
            ON
                a.user_id = u.id
            WHERE a.bed_id = b.id
        ) AS user_names
        FROM beds AS b"

The desired results would be:
[1, 'John Smith, Jane Doe']
[2, '']
[3, '']

I tried hardcoding the bed id and running this segment to get a list of names. It didn't work:

    sql = """
        (
        SELECT
            array_agg(user_name)
        FROM
            roomchoice_assignment AS a
        INNER JOIN
            roomchoice_customuser as u
        ON
            a.user_id = u.id
        WHERE
            a.bed_id = 1
        GROUP BY user_name
        )"""

It returned the following:

[
    [
        [
            "John Smith"
        ]
    ], 
    [
        [
            "Jane Doe"
        ]
    ]
]

I was hoping for this:

['John Smith, Jane Doe']

Answer 1

One issue with the query you have is that you're grouping by the column you're applying array_agg on. If you remove the group by you would get "{"John Smith","Jane Doe"}", but you would still be missing the bed id column, and if you want a list of all beds even if there are no assignments you should use left joins instead of subqueries (which also should be better for performance and readability).

You could use string_agg as indicated by the duplicate question.

This query:

SELECT b.id, string_agg(u.user_name, ', ') users
FROM beds AS b
LEFT JOIN assignment AS a ON a.bed_id = b.id
LEFT JOIN users as u ON a.user_id = u.id
GROUP by b.id;

Would give you a result like:

1;"John Smith, Jane Doe"
2;""
3;""