MySQL中数组内的JSON数据中获取值
MySQL中JSON数据获取值1.MySQL中JSON数据中获取值数据源:{"observeTruth": "111","preventHumenError": "DLYZ","twoIndex": "502","evaluate": "S"}sql语句:SELECTOBSERVE_TRUTH->'$**.twoIndex' as twoIndexJsonFROMPAS_OPERATION_
·
MySQL中JSON数据获取值
1.MySQL中JSON数据中获取值
数据源:
{
"observeTruth": "111",
"preventHumenError": "DLYZ",
"twoIndex": "502",
"evaluate": "S"
}
sql语句:
SELECT
JSON_EXTRACT(OBSERVE_TRUTH, '$.twoIndex') as twoIndexJson
from
PAS_OPERATION_MANAGEMENT_SCORE
WHERE
ID = 'c6fef7adc732449fb4c9c60920067083'
执行结果:
2.MySQL中数组内的JSON数据中获取值
数据源:
[
{
"observeTruth": "能力打分",
"preventHumenError": "DLYZ",
"twoIndex": "401",
"evaluate": "△"
},
{
"observeTruth": "222",
"preventHumenError": "DLYZ",
"twoIndex": "502",
"evaluate": "S"
}
]
sql语句:
SELECT
OBSERVE_TRUTH->'$**.twoIndex' as twoIndexJson
from
PAS_OPERATION_MANAGEMENT_SCORE
WHERE
ID = 'd35ae7966436458a959a569935007e0a'
执行结果:
将MySQL中的JSON数组转换为行
SELECT *
FROM
JSON_TABLE(
'[1, 2, 3]',
"$[*]"
COLUMNS(
Value INT PATH "$"
)
) data;
结果
set @delimited = 'a,b,c';
SELECT *
FROM
JSON_TABLE(
CONCAT('["', REPLACE(@delimited, ',', '", "'), '"]'),
"$[*]"
COLUMNS(
Value varchar(50) PATH "$"
)
) data;
结果
更多推荐
已为社区贡献2条内容
所有评论(0)