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

GMB · Accepted Answer · 2020-12-03 18:38:49Z

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.

answered Dec 3, 2020 at 18:38

GMB

224k25 gold badges103 silver badges151 bronze badges

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

4 Comments

Kim Rop Over a year ago

this does not give any error highlight but FUNCTION db.json_arrayagg does not exist on running

Philip Petrov Over a year ago

As GMB stated, you need MariaDB 10.5.0 or higher.

Philip Petrov Over a year ago

you can eventually simulate that function with: CONCAT('[', GROUP_CONCAT(JSON_OBJECT('id', h.id, 'name', h.name)),']')

Kim Rop Over a year ago

the latest xampp comes with 10.4.17-MariaDB so I cannot use it?

Collectives™ on Stack Overflow

mysql return nested array

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related