mysql5.7获取json数组中的某个对象

前言

表中的一个字段类型是字符串，存的是一个对象数据。
现在要根据对象中的某个属性，获取到整个对象信息。
如果是mysql8，则可以使用JSON_TABLE。
示例：https://blog.csdn.net/weixin_44071721/article/details/123347229

select '[{"k": "1", "v": "https://www.bilibili.com/video/BV1dU4y147t8?from=search&seid=17686981769015229460&spm_id_from=333.337.0.0c", "desc": "提升排名视频课程"}, {"k": "1", "v": "https://www.bilibili.com/video/BV1xq4y1G7Mj?from=search&seid=272572936245601262&spm_id_from=333.337.0.0", "desc": "提升排名视频课程"}, {"k": "2", "v": "https://www.bilibili.com/video/BV1Sq4y1C7Jc?from=search&seid=272572936245601262&spm_id_from=333.337.0.0", "desc": "xxxx视频课程"}, {"k": "2", "v": "https://www.bilibili.com/video/BV1Ws411P7Nu?from=search&seid=272572936245601262&spm_id_from=333.337.0.0", "desc": "xxxx视频课程"}]' into @aa;
SELECT @aa;select*
fromjson_table(@aa,'$[*]' columns (k varchar(500) path '$.k',`v` varchar(500) path '$.v',`desc` varchar(500) path '$.desc')) as tt
where tt.k = 1

但是5.7的没找到相关函数。

解决办法

SET @content = '[{"id":"882310097019154331","val":"质量安全部关闭全部电源，桌面清理干净，整理好个人工位！"},{"id":"882310099668482231","val":"市场运营部关闭全部电源，桌面清理干净，整理好个人工位！"},{"id":"882310095679757656","val":"研发生产中心（上海）关闭全部电源，桌面清理干净，整理好个人工位！无需工作的电脑、笔记本、主机等机器关闭关停！"},{"id":"882310092710195768","val":"研发生产中心（成都）关闭全部电源，桌面清理干净，整理好个人工位！无需工作的电脑、笔记本、主机等机器关闭关停！"},{"id":"882310091462264051","val":"物资保障部关闭全部电源，桌面清理干净，整理好个人工位！"},{"id":"882310098660146571","val":"项目工程部部关闭全部电源，桌面清理干净，整理好个人工位！"},{"id":"882310095734354651","val":"综合管理部关闭全部电源，桌面清理干净，整理好个人工位！"},{"id":"882310093022062399","val":"财务金融部部关闭全部电源，桌面清理干净，整理好个人工位！储物间清理干净！"}]';set @keyword = '882310099668482231';-- 获取到当前参数在json中的哪一个对象里面，它的下标是多少
select JSON_SEARCH( @content, 'one', @keyword )-- 去除下标两边的双引号
select TRIM('"' FROM JSON_SEARCH( @content, 'one', @keyword ))-- 去除多余的.id，得到目标对象坐标
select SUBSTRING( TRIM('"' FROM JSON_SEARCH( @content, 'one', @keyword )), 1, LOCATE( '.id', TRIM('"' FROM JSON_SEARCH( @content, 'one', @keyword )) ) - 1 ) -- 使用坐标获取对象信息
select JSON_EXTRACT(@content, SUBSTRING( TRIM('"' FROM JSON_SEARCH( @content, 'one', @keyword )), 1, LOCATE( '.id', TRIM('"' FROM JSON_SEARCH( @content, 'one', @keyword )) ) - 1 )  )-- 把sql做成存储过程
DELIMITER //
CREATE PROCEDURE GetJsonValue(IN content TEXT, IN keyword TEXT)
BEGINDECLARE keyword_path TEXT;-- 移除 content 中的双引号，因为 JSON_SEARCH 返回带引号的字符串SET keyword_path = TRIM('"' FROM JSON_SEARCH(content, 'one', keyword));-- 使用 SUBSTRING 函数截取所需的路径，并返回结果SELECT JSON_EXTRACT(content, SUBSTRING(keyword_path, 1, LOCATE('.id', keyword_path) - 1));
END //
DELIMITER ;-- 测试存储过程（json字符为对象或者数组都可以）
CALL GetJsonValue(@content, '882310091462264051');

在xml中使用

SELECTa.hazard_registration_id, a.hazard_registration_code,a.STATUS, a.description, a.attachments,a.register_by, DATE_FORMAT(a.register_time, '%Y-%m-%d %H:%i:%s') AS registerTimeStr,f.tracking_by, f.hazard_tracking_id,e.risk_point_type,case when b.type = 1 then TRIM('"' from JSON_EXTRACT(c.contentdetail , '$.val'))when b.type = 2 then concat(e.risk_point_name,'-', d.risk_source_name)end as position,case when b.type = 1 then a.responsible_person_bywhen b.type = 2 then d.charge_personend responsiblePersonId
FROMtb_hazard_registration a
LEFT JOIN tb_hazard_investigation_plan b ON a.hazard_investigation_plan_id = b.hazard_investigation_plan_id
LEFT JOIN (SELECTx.content_id,JSON_EXTRACT(y.content, JSON_UNQUOTE(SUBSTRING(TRIM('"' FROM JSON_SEARCH(y.content, 'one', x.content_id)), 1, LOCATE('.id', TRIM('"' FROM JSON_SEARCH(y.content, 'one', x.content_id))) - 1))) AS contentdetailFROMtb_hazard_registration xINNER JOIN tb_hazard_investigation_plan y ON x.hazard_investigation_plan_id = y.hazard_investigation_plan_id
) c ON a.content_id = c.content_id
LEFT JOIN tb_risk_source d on b.type = 2 and JSON_EXTRACT(c.contentdetail , '$.riskSourceId') = d.risk_source_id
LEFT JOIN tb_risk_point e on b.type = 2 and JSON_EXTRACT(c.contentdetail , '$.riskPointId') = e.risk_point_id
LEFT JOIN tb_hazard_tracking f ON a.hazard_registration_id = f.hazard_registration_id