In my stored procedure, I am processing records from specific tables. As part of this process, I am using a temporary table to store results generated by join operations.
Let's take a look at the structure of Table A:
+----+------+--------+
| id | name | number |
+----+------+--------+
| 1 | John | 123 |
| 2 | Tim | 567 |
| 3 | Bill | 789 |
| 4 | Jim | 345 |
+----+------+--------+
Now, let's explore Table B:
+----+------+--------+
| id | code | number |
+----+------+--------+
| 1 | LK | 123 |
| 2 | CN | 123 |
| 3 | BN | 789 |
| 4 | IN | 345 |
+----+------+--------+
Lastly, here is Table Temp which contains the combined result set:
+----+------+-----+------+--------+
| id | name | age | code | number |
+----+------+-----+------+--------+
| 1 | John | 54 | LK | 123 |
| 1 | John | 54 | CK | 123 |
| 3 | Bill | 26 | BN | 789 |
| 4 | Jim | 78 | IN | 345 |
+----+------+-----+------+--------+
The next step is to convert the result set in Table Temp to JSON format:
[{"id":1,"name":"John","code":"LK","number":123}, {"id":2,"name":"John","code":"CK","number":123}, {"id":3,"name":"Bill","code":"BN","number":789}, {"id":4,"name":"Jim","code":"IN","number":345}]
I now aim to display these records in a particular layout. Here's how I want it to appear:
+------------+-----+------+--------+
| name | age | code | number |
+------------+-----+------+--------+
| John | 54 | | |
| | | LK | 123 |
| | | CK | 123 |
| Bill | 26 | | |
| | | BN | 789 |
| Jim | 78 | | |
| | | IN | 345 |
+------------+-----+------+--------+
[{"name":"John","age":54}, {"code":"LK","number":123}, {"code":"CK","number":123}, {"name":"Bill","age":26}, {"code":"BN","number":789}, {"name":"Jim","age":78}, {"code":"IN","number":345}]
My question arises on how best to split and organize this JSON data for viewing purposes, or if there is a way to directly query and generate this structured result set from Table Temp in MySQL?