0

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.

1
  • If this is common, you should consider having pct_usage as a regular column.
    – Rick James
    Commented Apr 14, 2023 at 15:34

1 Answer 1

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

1
  • 1
    one of the few variations I didn't try, cheers.
    – IGGt
    Commented Apr 14, 2023 at 13:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.