How to parse a json object - mysql

Question

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).

Tim Biegeleisen · Accepted Answer · 2020-01-04 13:34:30Z

1

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

edited Jan 4, 2020 at 13:34

answered Jan 4, 2020 at 13:28

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

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

Comments

TsaiKoga · Accepted Answer · 2020-01-04 13:27:57Z

1

Use Mysql JSON_EXTRACT, the alias operator is ->,

Do something like this:

SELECT your_json_column->"$.\"2\".free_day"
FROM your_table
WHERE id = 1

More details: reference

answered Jan 4, 2020 at 13:27

TsaiKoga

13.4k2 gold badges23 silver badges31 bronze badges

Collectives™ on Stack Overflow

How to parse a json object - mysql

2 Answers 2

Demo

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related