Mysql5.7 + 查询并解析json数据方法（后转）

Stuild Stuil

已于 2023-05-31 17:17:26 修改

阅读量389

点赞数

CC 4.0 BY-SA版权

分类专栏： mysql 文章标签： mysql sql JSON_EXTRACT

于 2023-05-31 17:12:42 首次发布

本文链接：https://blog.csdn.net/qq_35086097/article/details/130973228

mysql 专栏收录该内容

10 篇文章

订阅专栏

文章介绍了在MySQL5.7中如何使用JSON_EXTRACT函数来查询和解析包含数组的JSON数据，特别是提取数组中特定元素的值。示例展示了如何获取所有子元素的价格。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

说明：本文是对 Mysql5.7 + 查询并解析json数据方法的补充说明

具体请点击查看

当前，也可以认为就是水贴

对于某个属性的值未数组的时候，我们取某一条中某一条某个元素，大家应该都理解了，具体

如下（详情请看上一篇 Mysql5.7 + 查询并解析json数据方法）：

SELECT
  JSON_EXTRACT(
    '{"ladder":true,"useFirstSurplusDosage":0.0,"useSecondSurplusDosage":0.0,"ladderConfig":[{"price":2800,"dosage":242.0,"increment":"60"}, {"price":3360,"dosage":1800.0,"increment":"60"}, {"price":4200}, {}, {}],"gasValue":34.0,"detail":[{"price":2800,"cur":0,"fee":95200,"dosage":34.0}],"fee":95200}',
    '$.ladderConfig'
  )
FROM
  DUAL

SELECT
JSON_EXTRACT(
  JSON_EXTRACT(
    '{"ladder":true,"useFirstSurplusDosage":0.0,"useSecondSurplusDosage":0.0,"ladderConfig":[{"price":2800,"dosage":242.0,"increment":"60"}, {"price":3360,"dosage":1800.0,"increment":"60"}, {"price":4200}, {}, {}],"gasValue":34.0,"detail":[{"price":2800,"cur":0,"fee":95200,"dosage":34.0}],"fee":95200}',
    '$.ladderConfig'
  ),'$[0]')
FROM
  DUAL

但是我就想获取所有的子元素中某个元素的值，那该怎么取呢？

如下

SELECT
 
  JSON_EXTRACT(
    '{"ladder":true,"useFirstSurplusDosage":0.0,"useSecondSurplusDosage":0.0,"ladderConfig":[{"price":2800,"dosage":242.0,"increment":"60"}, {"price":3360,"dosage":1800.0,"increment":"60"}, {"price":4200}, {}, {}],"gasValue":34.0,"detail":[{"price":2800,"cur":0,"fee":95200,"dosage":34.0}],"fee":95200}',
    '$.ladderConfig[*].price'
  )
FROM
  DUAL

当然，前后带[]符号也很好解决啊直接替换

 SELECT
 REPLACE(
 REPLACE(
  JSON_EXTRACT(
    '{"ladder":true,"useFirstSurplusDosage":0.0,"useSecondSurplusDosage":0.0,"ladderConfig":[{"price":2800,"dosage":242.0,"increment":"60"}, {"price":3360,"dosage":1800.0,"increment":"60"}, {"price":4200}, {}, {}],"gasValue":34.0,"detail":[{"price":2800,"cur":0,"fee":95200,"dosage":34.0}],"fee":95200}',
    '$.ladderConfig[*].price'
  ),'[',''),']','')
FROM
  DUAL