MySQL - json_extract() and json_table()

{"widges":[{"id": "aaa", "type": "type_a"}, {"id": "bbb", "type": "type_b"}, {"id": "ccc", "type": "type_c"}]}"

对于上面的json，如何用MySQL 的JSON_EXTRACT()函数SELECT出id="ccc"的type的值？

 

ChatGPT said:

在 MySQL 中，如果你有一个 JSON 字段（例如 data），内容如下：

{"widges": [{"id": "aaa", "type": "type_a"},{"id": "bbb", "type": "type_b"},{"id": "ccc", "type": "type_c"}]
}

 

并且你想通过 JSON_EXTRACT() 查询出 id = "ccc" 的 type 值（即 type_c），
可以使用 MySQL 8.0 的 JSON_TABLE()（推荐）或纯 JSON_EXTRACT()+JSON_SEARCH() 的组合。

---

✅ 方法 1：使用 JSON_TABLE()（推荐，语义清晰）

SELECT jt.type
FROM your_table,
JSON_TABLE(data,'$.widges[*]'COLUMNS (id   VARCHAR(50) PATH '$.id',type VARCHAR(50) PATH '$.type')
) AS jt
WHERE jt.id = 'ccc';

👉 输出结果：

type_c

---

✅ 方法 2：仅用 JSON_EXTRACT() + JSON_SEARCH()（兼容旧版本）

如果你的 MySQL 版本 < 8.0.4（不支持 JSON_TABLE），可以用：

SELECT JSON_EXTRACT(data,CONCAT('$.widges[', JSON_UNQUOTE(JSON_SEARCH(data, 'one', 'ccc', NULL, '$.widges[*].id')), '].type')
) AS type_value
FROM your_table;

但注意，这种写法较复杂，需要一些细节调整。

为了让它可运行，我们拆解一下逻辑：

完整可执行版本：

SELECT JSON_EXTRACT(data,REPLACE(JSON_UNQUOTE(JSON_SEARCH(data, 'one', 'ccc', NULL, '$.widges[*].id')),'.id','.type')
) AS type_value
FROM your_table;

👉 输出结果：

"type_c"

---

✅ 总结

方法	适用版本	优点	示例结果
JSON_TABLE()	MySQL ≥ 8.0.4	简洁清晰	type_c
JSON_SEARCH() + JSON_EXTRACT()	MySQL ≥ 5.7	向下兼容	"type_c"（带引号）