CODEWITHSUNDEEP
mysqlmysql-workbenchdatabase-administrationmysqladministratormysql-dependent-subquery
Hitesh
Hitesh

Reputation: 243

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

Table 1

enter image description here

Table 2

enter image description here

I want this kind of output needed in Format enter image description here

+

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 .....!

Upvotes: 1

Views: 653

Answers (1)

Ed Bangga
Ed Bangga

Reputation: 13006

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;

Upvotes: 0

Related Questions