How to get a one-to-many relationship in Sql, where many will be an array at once?

Question

MySQL

Good day everyone, the essence of the matter is, you need to take the lines with their comments from the database, but so that the comments are like an array or an object.
here is the request

SELECT *, ST_AsText(geo) as geo, 
                        ST_DISTANCE(ST_GeomFromText('LINESTRING(
                        $data->d15)'), geo) * 100 AS distance,
                        cities.name as city_name,
                        spots.name as name,
                        spots.lng as lng,
                        spots.lat as lat,
                        spots.id as id
                        spots_comments.created_at as comment_date
                        FROM spots
                        LEFT JOIN cities ON  spots.id_city = cities.id
                        LEFT JOIN spots_comments ON  spots.id  = spots_comments.spot_id
                        WHERE category = 1
                        HAVING distance < 0.3
                        ORDER BY distance

how to do conditionally like this
obj(id:123, name: qwe , comments : [1:qwe , 2: qweq ...])
was a variant to use group_concat on comments, and already at the front to parse by the separator, but this is an idea maybe xs how to set a limit on the selection of comments, conditionally if there are 500+ of them, parsing will take a long time, especially since they will be updated every 20 seconds ...
there is a variak how to do this?
Or is it possible to somehow make such a request in Yii? To immediately pick up with a connection?

Answer 1

1) Aggregation functions of the group buy type have a very serious effect on the query time.
2)

if there are 500+ of them, parsing will take a long time

Naturally, according to the mind, you need to carry only fresh / updated ones, otherwise the zhpa will definitely come. And with fresh ones, of course, the sample will already be quite small.
Hint: I wouldn't bother with json or aggregation in general in the request, I think array parsing on the back will be much faster than the request, which, in principle, is not difficult to check if there is a ready-made database.

Answer 2

IF(COUNT(`spots_comments`.`id`) = 0, '[]', CONCAT(
    '[', 
    GROUP_CONCAT(JSON_OBJECT(
        'id', `spots_comments`.`id`,
        'created', `spots_comments`.`created`,
        'current', `spots_comments`.`text`,
    ) ORDER BY `spots_comments`.`created` DESC),
    ']'
)) AS 'lastComments'