mysql json数组对象，单独提取某一个对象并提取某一个属性数据

如字段 detail_data 值类型：

[{“name”: “技术活动名称”, “value”: “活动1”}, {“name”: “计划评审时间”, “value”: “2021-03-20”}, {“name”: “项目名称”, “value”: “修改编辑时的选人问题”}, {“name”: “项目类别”, “value”: “地震勘探”}, {“name”: “成果名称”, “value”: “成果名称”}, {“name”: “评审主持人”, “value”: “[“215815092837727976”]”}, {“name”: “工作类别”, “value”: “建设”}, {“name”: “内容”, “value”: “内容”}, {“name”: “表格”, “value”: “1”}, {“name”: “费用合计”, “value”: “102917.1”}, {“name”: “会议通知”, “value”: “通知”}]

在这里插入图片描述

我需要把全部的费用合计求和统计

{“name”: “费用合计”, “value”: “102917.1”}

SELECT
	SUM(
		JSON_EXTRACT( detail_data, JSON_UNQUOTE( REPLACE ( JSON_SEARCH( detail_data, 'all', '费用合计', NULL, '$[*].name' ), 'name', 'value' ) ) ) 
	) AS sum
FROM
	project_approve_info 
WHERE
	type = '财务管理-生产专家咨询';

在这里插入图片描述

mysql json字段 某个属性的值模糊查询


 SELECT
  * 
FROM
  project_approve_info 
WHERE
  type = '财务管理-生产专家咨询' 
  AND JSON_EXTRACT( detail_data, 
  JSON_UNQUOTE( 
  REPLACE ( JSON_SEARCH( detail_data, 'all', '费用合计', NULL, '$[*].name' ), 'name', 'value' ) ) ) LIKE "%23%";

value值为json数组格式的字符串，需要与其他json数组合并

SELECT
	JSON_MERGE_PRESERVE (
		attachment,
		JSON_UNQUOTE (
			JSON_EXTRACT( detail_data, JSON_UNQUOTE( REPLACE ( JSON_SEARCH( detail_data, 'all', '附件', NULL, '$[*].name' ), 'name', 'value' ) ) ) 
		) 
	) '附件' 
FROM
	project_approve_info 
WHERE
	project_id = 43 
	AND type = '设计书审查'

mysql json数组对象，单独提取某一个对象并提取某一个属性数据

推荐阅读