MySQL中数组内的JSON数据中获取值

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;

结果