记一次:mysql统计的CAST函数与json字段中的某个字段

前言：因为需求的问题，会遇到将某个json存入到一个字段中，但在统计的时候，又需要将这个json中的某个字段作为条件来统计，所以整理了一下cast函数和json中某个字段的条件判断

一、浅谈mysql的json

1.1 上例子

SELECT
		lide.id,
		lide.event_values -> '$.MeasureValue' AS MeasureValue,
		lide.event_values -> '$.MeasureIndex_Name' AS MeasureIndex_Name,
		lide.event_values -> '$.deviceName' AS deviceName,
		lide.create_time 
	FROM
		lhkj_iot_driver_event lide

注意：上面的 -> 可以用JSON_EXTRACT(lide.event_values, '$.MeasureValue') 代替，如下

SELECT
		lide.id,
		JSON_EXTRACT(lide.event_values, '$.MeasureValue')  AS MeasureValue,
		JSON_EXTRACT(lide.event_values, '$.MeasureIndex_Name') AS MeasureIndex_Name,
		JSON_EXTRACT(lide.event_values, '$.deviceName') AS deviceName,
		lide.create_time 
	FROM
		lhkj_iot_driver_event lide

1.2 解析

mysq json 主要有JSON 对象（json object ）和JSON 数组（json array ）两种类型

$表示整个json对象，在索引数据时用下标（对于json array，从0开始）或键值（对于json object，含有特殊字符的key要用"括起来，比如$.“my name”）

根据1.1的筛选后面的json条件参数就可以筛选了

1.3 扩展--mysql相关的json函数

二、浅谈CAST函数

2.1 上例子

SELECT
	CAST( create_time AS date ) AS compareDate,
	ROUND( AVG( MeasureValue ), 3 ) AS averageValue 
FROM
	(
	SELECT
		lide.id,
		JSON_EXTRACT(lide.event_values, '$.MeasureValue')  AS MeasureValue,
		JSON_EXTRACT(lide.event_values, '$.MeasureIndex_Name') AS MeasureIndex_Name,
		JSON_EXTRACT(lide.event_values, '$.deviceName') AS deviceName,
		lide.create_time 
	FROM
		lhkj_iot_driver_event lide
	WHERE
		lide.create_time BETWEEN '2022-04-01' AND '2024-05-01'
	AND lide.identifier = 'Rept_MeasureData'
	) t
        group by compareDate;

个人理解：一般CAST要和分组group by组合使用，不仅仅是cast函数，一般的函数都要分组使用不然的话统计出来的只有一条数据往往不是我们想要的结果

2.2 解析

CAST( create_time AS date )是按日期进行转换的，那么也可以按年，按月等如下

SELECT 
  CAST(create_time  AS CHAR(7)) AS month_as_date,
	YEAR(CAST(create_time AS DATE)) AS year_number,
  MONTH(CAST(create_time AS DATE)) AS month_number,
	ROUND( AVG( MeasureValue ), 3 ) AS averageValue

但这也不是我想要的结果，如果按年-月统计的话我又该如何应对？所以可以用大招， CHAR(数值)

在MySQL中，CAST函数可以用来转换或者格式化时间数据类型。我们想要将这个时间格式化为'YYYY-MM-DD HH:MM:SS'格式。

SELECT CAST(event_datetime AS CHAR(19)) AS formatted_datetime
FROM events;
如果你想要将时间转换为其他格式，你可以更改CHAR(19)为其他的字符长度，并相应地调整格式字符串。例如，如果你想要转换为'YYYY-MM-DD'格式，你可以这样做：

SELECT CAST(event_datetime AS CHAR(10)) AS formatted_date
FROM events;
请注意，CAST函数的具体实现可能会依赖于MySQL的版本，上述代码在MySQL 5.x和更高版本中通常有效。