A solution closer in spirit to what you were trying to do looks like this:

select coalesce(
  ( select value from mytab where name = 'bad_phone' ),
  ( select value from mytab where name = 'cell_phone' )
)    -- as some_alias
from  dual;

Here the SELECT VALUE ... subqueries are in the outer SELECT clause, not the FROM clause. Used like that in SELECT, they are supposed to return a single value (that is: one column in one row); if the query returns no rows, SQL interprets that as NULL, as you were trying to do. If either "scalar" subquery returns more than one row, you'll get an error, as you probably should. (Perhaps that is not even possible, through constraints on MYTAB.)

Note that the outer query selects from DUAL - only the scalar subqueries are against the real "data" table.

When accessing a field in a JSON object, by default a missing field returns a NULL value, which is what you want here. Not necessarily the "best" way, but it does avoid a bit of repetition in the code.

with json_data(j) as (
  select json_objectagg(name value value) from mytab
)
select coalesce(jd.j.home_phone, jd.j.cell_phone, 'none') phone
from json_data jd
union all
select coalesce(jd.j.bad_phone, jd.j.cell_phone, 'none') phone
from json_data jd;

PHONE       
867-5309    
867-5309 

Best regards,
Stew

Not sure this is the "best" way, but it is a good way (I think).

I assume the end user will search for a phone number (perhaps for a single customer, identified by ID) by giving several options for "name" and an order of priority. This can be done in various ways in the user interface; since that isn't really the question here, I will simulate it with a subquery named REQUEST in the WITH clause. Given the list 'bad_phone', 'home_phone', 'cell_phone', we are looking for the first non-null phone number matching one of the phone types, in the order given.

Here's how I would do that:

with
  request (type, priority) as (
    select 'bad_phone' , 1 from dual union all
    select 'home_phone', 2 from dual union all
    select 'cell_phone', 3 from dual
  )
select min(m.name)  keep (dense_rank first order by r.priority) as name,
       min(m.value) keep (dense_rank first order by r.priority) as value
from   mytab m join request r on m.name = r.type
where  m.value is not null
;

Note that if none of the names match, or if the only names that do match have NULL value, the query will still return one row: both NAME and VALUE will be NULL. If this is not the desired output (although that IS the output from the other solutions offered so far), you can add GROUP BY NULL at the end of the query; then the query will return no rows. This is a subtle difference between a query with aggregate functions with or without a trivial GROUP BY clause, when there are no rows surviving to the GROUP BY step.

Alternatively, if the query is to return nothing if there is no non-NULL value matching any of the inputs, the query can be written more simply like this:

with
  request (type, priority) as (
    select 'bad_phone' , 1 from dual union all
    select 'home_phone', 2 from dual union all
    select 'cell_phone', 3 from dual
  )
select name, value
from   mytab m join request r on m.name = r.type
where  m.value is not null
order  by r.priority
fetch  first row only
;

I expect this to be less efficient (it uses analytic ROW_NUMBER() under the hood, and analytic functions are generally slower than aggregate functions), although that is probably undetectable unless the number of input "names" to search for (given here in the WITH clause) is large.