I have a table with headers that include the id and name of individuals. Additionally, there are multiple tables containing specific details about each individual, such as phone number, address, gender, birthplace, etc.
How can I construct a SELECT command that joins all these tables, but returns the data in one JSON populated column?
The desired output format is: {"id":''236732", "name":"John Doe", "phone": "4875696", ... }
Is it possible to achieve this?
table_header
id name
1 John Doe
2 Jack Sparrow
3 Jane Doe
....
...
....
5000 Jane Silk
table_detail id phone_number 1 34574358 2 68538039 3 7537689237 .... ... ... 5000 89457987
table_detail2 id address 1 xxxxxxxx 2 xxxxxxxx 3 xxxxxxxx .... .... .... 5000 xxxxxxx
table_detail3 .... .... ..... .... .... table_detail10 (with nearly identical structures, containing over 5000 records each)
I aim to add an additional field of JSON type to the table_header, which consolidates all detailed information for each user.