MySQL. How to aggregate sub-query to JSON?

Question

MySQL

Is it possible somehow in MySQL to aggregate subqueries in JSON format?
For example, as PgSQL:

SELECT t1.id, json_agg((SELECT * FROM t2 WHERE t2.id = t1.id)) FROM t1;

Thank you!

Answer 1

in MySQL, if I'm not mistaken, there is no analogue of json_agg, but you can solve this problem, for example, by concatenation, like this (the request was written on the knee as an example of the direction where to move):

SELECT 
    t1.id, 
    CONCAT('[{',
    GROUP_CONCAT( 
      (SELECT CONCAT('field1: ', t2.field1, ', field2: ' t2.field2) FROM t2 WHERE t2.id = t1.id)  
       SEPARATOR ' }, { '
   ), '}]'
FROM t1 GROUP BY t1.id

PS I do not recommend using nested queries, it is better to use JOIN