Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.5.4
-
Ubuntu 18.04LTS Server, MariaDB 10.5.4
Description
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"} |
Attachments
Issue Links
- relates to
-
MDEV-22084 Squared brackets missing from JSON_ARRAYAGG when used in a view
- Closed