Convert JSON field to key value pair in mysql

Question

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.

LoicM · Accepted Answer · 2021-05-27 14:50:27Z

2

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

edited May 27, 2021 at 14:50

LoicM

2,2892 gold badges25 silver badges48 bronze badges

answered May 26, 2021 at 16:18

Akhilesh Mishra

6,1503 gold badges20 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

LoicM Over a year ago

Great, thank you ! For future readers, note that if your class name contains spaces, the json path created in the JSON_EXTRACT needs to be encapsulated in double quotes as follow CONCAT('$."', class, '"')

LoicM Over a year ago

Another question out of curiosity: is the cast of the json_keys as json necessary ? I tested without it on my use case and it works fine

Akhilesh Mishra Over a year ago

It is not mandatory. Actually JSON_KEYS() return is sufficient.

Collectives™ on Stack Overflow

Convert JSON field to key value pair in mysql

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related