By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE employees(id INT PRIMARY KEY, name VARCHAR(45), age INT);
INSERT INTO employees(id, name, age) VALUES (1,'John', 34);
INSERT INTO employees(id, name, age) VALUES (2,'Mary', 40);
INSERT INTO employees(id, name, age) VALUES (3,'Mike', 44);
SET @jsonempl=(SELECT JSON_ARRAYAGG(
JSON_OBJECT("id", id, "name", name, "age", age))
FROM employees);
SELECT JSON_PRETTY(@jsonempl);
| JSON_PRETTY(@jsonempl) |
|---|
| [ { "id": 1, "age": 34, "name": "John" }, { "id": 2, "age": 40, "name": "Mary" }, { "id": 3, "age": 44, "name": "Mike" } ] |
SELECT JSON_PRETTY(JSON_ARRAYAGG(
JSON_OBJECT("id", id, "name", name, "age", age)
)) json
FROM employees
| json |
|---|
| [ { "id": 1, "age": 34, "name": "John" }, { "id": 2, "age": 40, "name": "Mary" }, { "id": 3, "age": 44, "name": "Mike" } ] |