how to access JSON data from a mysql JSON column

Question

I have the following entry in a JSON field in MySQL, How can I access the wid_1 -> pct_usage data?

{ "wid_1": { "cof": 1, "pct_usage": 50.0, "tid": 69285334, "wid": 1 }, "wid_2": { "cof": 1, "pct_usage": 50.0, "tid": 69285335, "wid": 2 } }

I tried various variations of:

json_unquote(json_extract(s.`col_name`,'$.wid_1'.'pct_usage')) as wid_1_pct,
json_unquote(json_extract(s.`col_name`,'$.wid_1','pct_usage')) as wid_1_pct,
json_unquote(json_extract(s.`col_name`,'$.wid_1'{'pct_usage'})) as wid_1_pct, 
json_unquote(json_extract(s.`col_name`,'$.wid_1','{pct_usage}')) as wid_1_pct,

but none of them work.

• Rick James: If this is common, you should consider having pct_usage as a regular column.

Answer 1

This one works for me:

SELECT s.col_name,
json_extract(s.col_name, '$.wid_1.pct_usage') as wid_1_pct
FROM t1 as s

https://www.db-fiddle.com/f/466NTsurpc9YKbrbQ5LvaB/0

• IGGt: one of the few variations I didn't try, cheers.