mysql json嵌套数组如何提取其中的值

Question

MySQL如何在json嵌套数组中提取值

{"$custom":{"sopCateId":21254045,"srType":300608401,"isCMS":true,"commonQueueId":999999},
"actionKeyMemo":[{"dataKey":"cateName","value":"入住/退房问题咨询 ","key":"会员问题"},{"dataKey":"bizId","value":"3165229512091656766","key":"订单编号"},{"dataKey":"contactName","value":"潘越海","key":"会员称呼"},{"dataKey":"ContactPhone","value":"17376048017","key":"联系方式"},{"dataKey":"WbbpHAarCM","value":"用户表示下单后 前台查询不到入住信息","key":"用户诉求"},{"dataKey":"preReply","value":"","key":"小二答复"},{"dataKey":"pfCanAutoFinish","value":"","key":"是否开启赔付托管"},{"dataKey":"pfAutoFinishStatus","value":"","key":"赔付托管结果"},{"dataKey":"Promise","value":"2023-01-25 17:44:44","key":"承诺回复时效"},{"dataKey":"DefaultRepl","value":"","key":"默认回复时效"}],"operatorNick":"淘枣科","acceptorNick":"潘潘318"}
该数组中，
如何将 {"dataKey":"Promise","value":"2023-01-25 17:44:44","key":"承诺回复时效"} 的时间数据"2023-01-25 17:44:44" 提取出来

Answer 1

使用MySQL函数JSON_EXTRACT即可从嵌套的JSON数组中提取值。

SELECT JSON_EXTRACT('{"$custom":{"sopCateId":21254045,"srType":300608401,"isCMS":true,"commonQueueId":999999},
"actionKeyMemo":[{"dataKey":"cateName","value":"入住/退房问题咨询 ","key":"会员问题"},{"dataKey":"bizId","value":"3165229512091656766","key":"订单编号"},{"dataKey":"contactName","value":"潘越海","key":"会员称呼"},{"dataKey":"ContactPhone","value":"17376048017","key":"联系方式"},{"dataKey":"WbbpHAarCM","value":"用户表示下单后 前台查询不到入住信息","key":"用户诉求"},{"dataKey":"preReply","value":"","key":"小二答复"},{"dataKey":"pfCanAutoFinish","value":"","key":"是否开启赔付托管"},{"dataKey":"pfAutoFinishStatus","value":"","key":"赔付托管结果"},{"dataKey":"Promise","value":"2023-01-25 17:44:44","key":"承诺回复时效"},{"dataKey":"DefaultRepl","value":"","key":"默认回复时效"}],"operatorNick":"淘枣科","acceptorNick":"潘潘318"}',
'$.actionKeyMemo[9].value') AS Promise_Time;