mysql return nested array

Question

I have this SQL

SELECT
    U.id,
    U.first_name,
    hobby.id,
    hobby.name
FROM
    USER AS U
INNER JOIN user_hobbies AS UH
ON
    UH.user_id = U.id
INNER JOIN hobby ON hobby.id = user_hobbies.hobby_id FOR JSON AUTO

am trying to run in in xampp PHP myadmin and am getting an error

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'JSON AUTO' at line 11

the FOR JSON AUTO is marked red even in SQL input could anyone help on how to so such inner join, return inner array

Answer 1

FOR JSON AUTO is SQL Server syntax, that is not supported in MySQL. If you are looking to generate one row per user, along with a JSON array of hobbies objects, you can use JSON aggregation like so:

select u.id, u.first_name,
    json_arrayagg(json_object('id', h.id, 'name', h.name)) as hobbies
from users u 
inner join user_hobbies uh on h.user_id = u.id
inner join hobbies h on h.id = uh.hobby_id
group by u.id

Note that this requires MariaDB 10.5.0 or higher.