How to parse a json object – mysql

I have a json object like this:

{"1": {"penalty_percent": 3, "free_day": 5, "free_hours": 24}, "2": {"penalty_percent": 2, "free_day": 5, "free_hours": 12}, "3": {"penalty_percent": 2, "free_day": 2, "free_hours": 36}, "4": {"penalty_percent": 3, "free_day": 3, "free_hours": 48}, "5": {"penalty_percent": 5, "free_day": 2, "free_hours": 1}, "18": {"penalty_percent": 5, "free_day": 2}, "30": {"penalty_percent": 5, "free_day": 5, "free_hours": 10}}

I want to get free_day value from key object is 2. (at this here is 5).

Answer

Using JSON_EXTRACT:

SELECT JSON_EXTRACT(JSON_EXTRACT(json, '$."2"'), "$.free_day")
FROM yourTable;

Note that because you are using numbers as JSON keys, they need to be escaped in double quotes.

Demo