By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table table_name (id int, url varchar(100) null, urls varchar(200))
insert into table_name values
(6433, null, '["https://do.foo/", "https://do.foo/2"]')
select * from table_name
| id | url | urls |
|---|---|---|
| 6433 | null | ["https://do.foo/", "https://do.foo/2"] |
select coalesce(url, '') = '' as `is_true` ,
convert(JSON_EXTRACT(urls, '$[0]'), CHAR) as `extracted`,
case
when coalesce(url, '') = '' then convert(JSON_EXTRACT(urls, '$[0]'), CHAR)
else '3'
end as `valid_url`
from table_name where id = 6433
| is_true | extracted | valid_url |
|---|---|---|
| 1 | "https://do.foo/" | "https://do.foo/" |
select coalesce(url, '') = '' as `is_true` ,
convert(JSON_EXTRACT(urls, '$[0]'), CHAR) as `extracted`,
case coalesce(url, '')
when '' then convert(JSON_EXTRACT(urls, '$[0]'), CHAR)
else '3'
end as `valid_url`
from table_name where id = 6433
| is_true | extracted | valid_url |
|---|---|---|
| 1 | "https://do.foo/" | "https://do.foo/" |