By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
SELECT VERSION();
| VERSION() |
|---|
| 10.3.36-MariaDB-1:10.3.36+maria~deb10-log |
DROP TABLE IF EXISTS `user`;
CREATE TABLE IF NOT EXISTS `user`(
`id` SERIAL,
`name` VARCHAR(50),
`active` BOOL
);
INSERT INTO `user` (`name`, `active`)
VALUES ('Name1', true), ('Name2', true), ('Name3', true);
Records: 3 Duplicates: 0 Warnings: 0
SELECT
IFNULL(
(SELECT
CONCAT('[', GROUP_CONCAT(
JSON_OBJECT(
'id', `user`.`id`,
'name', `user`.`name`
)
), ']')
FROM `user`
WHERE `user`.`active` = false),
JSON_ARRAY()
) `json_users_active_false`,
IFNULL(
(SELECT
CONCAT('[', GROUP_CONCAT(
JSON_OBJECT(
'id', `user`.`id`,
'name', `user`.`name`
)
), ']')
FROM `user`
WHERE `user`.`active` = true),
JSON_ARRAY()
) `json_users_active_true`;
| json_users_active_false | json_users_active_true |
|---|---|
| [] | [{"id": 1, "name": "Name1"},{"id": 2, "name": "Name2"},{"id": 3, "name": "Name3"}] |