MySQL 8.0

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

select version();


version()


8.0.36


create table test(id int, value_ text);
insert into test values(1,'{"class1": 0.99, "class2": 0.05}');
insert into test values(2,'{"class1": 0.94, "class2": 0.01, "class3": 0.4}');
select id,class, json_extract(value_,concat('$.',class)) confidence from  test t1
cross join 
json_table(cast(json_keys(value_) as json), '$[*]' columns(class varchar(10) path '$'))t2


id
class
confidence


1
class1
0.99

1
class2
0.05

2
class1
0.94

2
class2
0.01

2
class3
0.4