MariaDB 10.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table jobs (
  id integer primary key,
  name varchar(255)
)
insert into jobs values (1, 'Foo'), (2, 'Bar'), (3, 'Baz')

Records: 3  Duplicates: 0  Warnings: 0

select json_object('id', id, 'name', name) from jobs where id = 2;

json_object('id', id, 'name', name)

{"id": 2, "name": "Bar"}

select json_arrayagg(json_object('id', id, 'name', name)) from jobs where id in (1, 3)

json_arrayagg(json_object('id', id, 'name', name))

[{"id": 1, "name": "Foo"},{"id": 3, "name": "Baz"}]