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
	*
from
	json_table(@aa,'$[*]' 
	columns (k varchar(500) path '$.k',
	`v` varchar(500) path '$.v',
	`desc` varchar(500) path '$.desc'
	)) as tt
where tt.k = 1
1
2
3
4
5
6
7
8
9
10
11
12

但是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)
BEGIN
    DECLARE 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');

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

在xml中使用

SELECT
    a.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_by
        when b.type = 2 then d.charge_person
    end responsiblePersonId
FROM
    tb_hazard_registration a
LEFT JOIN tb_hazard_investigation_plan b ON a.hazard_investigation_plan_id = b.hazard_investigation_plan_id
LEFT JOIN (
    SELECT
        x.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 contentdetail
    FROM
    tb_hazard_registration x
    INNER 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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27