When using without the GROUP BY, the square brackets are there, but when I add GROUP BY to the query the square brackets disappear.
create table owner(
|
id int primary key,
|
name varchar(50)
|
);
|
|
create table test_table(
|
id int primary key,
|
type varchar(50),
|
size int,
|
color varchar(50),
|
owner_id int,
|
FOREIGN KEY(owner_id) REFERENCES owner(id)
|
);
|
|
SELECT name, owner.id, JSON_ARRAYAGG(JSON_OBJECT('id',tt.id,'type',tt.type,'color',tt.color)) as materials from owner LEFT JOIN test_table tt on owner.id = tt.owner_id GROUP BY owner.id;
|
Expected result:
| Name |
Id |
Materials |
| David |
1 |
[{"id": 2, "type": "metal", "color": "blue"},{"id": 1, "type": "paper", "color": "red"}] |
| Joe |
2 |
[{"id": 3, "type": "wood", "color": "green"}] |
| John |
3 |
[{"id": 4, "type": "plastic", "color": "yellow"}] |
Actual result:
| Name |
Id |
Materials |
| David |
1 |
{"id": 2, "type": "metal", "color": "blue"}
,
{"id": 1, "type": "paper", "color": "red"} |
| Joe |
2 |
{"id": 3, "type": "wood", "color": "green"} |
| John |
3 |
{"id": 4, "type": "plastic", "color": "yellow"} |
Bug
Critical
