主页 / dba / 问题 / 326012
Accepted
IGGt
Asked: 2023-04-14 18:40:36 +0800 CST

如何从 mysql JSON 列访问 JSON 数据

  • 772

我在 MySQL 的 JSON 字段中有以下条目，如何访问 wid_1 -> pct_usage 数据？

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

我尝试了各种变体：

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,

但它们都不起作用。

mysql

1 个回答

  1. Best Answer

    这个对我有用：

    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

相关问题