By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select json_array (
case when Cat = 'C' then 'cat' end,
case when Dog = 'D' then 'dog' end,
case when Lizard = 'L' then 'lizard' end,
case when Mouse = 'M' then 'mouse' end) as pets
from (
select 'Hosp2' as hospital,
'C' as cat,
'D' as dog,
'L' as lizard,
null as mouse
from dual
) x
where Hospital = 'Hosp2';
| PETS |
|---|
| ["cat","dog","lizard"] |