Convert JSON field to key value pair in mysql

user_7505228 0
Viewed 369

I have predictions stored in a JSON field in a mysql table as follow

SELECT id, value from predictions

id | value                 |
+--------------------------+
1  | {"class1": 0.99, "class2": 0.05}
2  | {"class1": 0.94, "class2": 0.01, "class3": 0.4}
...

Each row contains a dictionary with (potentially) different keys, but each dictionnary follows this schema.

I'd like to deserialize this into a key-value result like below:

id | class  | confidence |
+------------------------+
1  | class1 | 0.99       |
1  | class2 | 0.05       |
2  | class1 | 0.94       |
2  | class2 | 0.01       |
2  | class3 | 0.4        |

Were the JSON in the format of a list containing one dictionary for each predicted class, I could have used the JSON_TABLE as I am using MySQL > 8.0, however I am at a loss on how to do so.

mysql sql json
1 Answers

As you are using MySql 8.0 you can use JSON_KEYS(), JSON_EXTRACT() and JSON_TABLE for your requirement like below:

SELECT 
    id,
    class, 
    json_extract(value_,concat('$.',class)) confidence 
from test t1
cross join 
    json_table(
        json_keys(value_)
        , '$[*]' columns(class varchar(10) path '$')
    ) t2

DEMO

user_77724830
Related