how to get select sub-query in which another table whole data get in json format

Question

Table 1

Table 2

I want this kind of output needed in Format

+

My Current Query this

SELECT *, (SELECT id FROM tb1 where id = 1) as data_json FROM `tb5`

i want get table tb1 whole data in one column like that

id | col1 | col2 | col3 | col4 | data_json
 1 |  ab  |  ab  |  uo  | ty   | [{'col1':1, 'col2':'ok', 'col3':'yy'},{'col1':1, 'col2':'ok', 'col3':'yy'}...]

how to change logical here tell me

how can get here whole json data from tb5 i don't want use join and specific column mention want whole tb5

help me .....!

Answer 1

we will need these json functions to transform your data.

select t1.*,
    json_arrayagg(json_object('col1', t2.col1, 'col2', t2.col1, 'col3', t2.col3)) as data_json
from tb5 t1
inner join tb1 t2 on t2.id = t1.id
where t2.id = 1;