如果一个字段里面存着一个数组,如何拿到这个数组里面对应的值。
CREATE DEFINER = 'root'@'%'
FUNCTION GetValueFromJSONArray(InJSONArray varchar(4000), InDataName varchar(50))
RETURNS DECIMAL(13, 1)
DETERMINISTIC
BEGIN
SELECT
Value INTO @value
FROM JSON_TABLE
(
InJSONArray,
'$[*]' COLUMNS
(
HeadName varchar(100) PATH '$.HeadName',
Value decimal(13, 1) PATH '$.Value'
)
) AS t
WHERE HeadName = InDataName
LIMIT 1;
RETURN @value;
END
以下是JSON格式
[
{
"HeadName": "温度1",
"Value": 100.0
},
{
"HeadName": "温度2",
"Value": 100.0
},
{
"HeadName": "温度3",
"Value": 22.0
},
{
"HeadName": "温度4",
"Value": 344.0
},
{
"HeadName": "温度5",
"Value": 100.0
},
{
"HeadName": "温度6",
"Value": 100.0
},
{
"HeadName": "温度7",
"Value": 22.0
},
{
"HeadName": "温度8",
"Value": 344.0
}
]