##示例字段 JSON_TEXT ,格式化后数据:
{
"SurveyResult":[
{
"SurveyObjType":"0",
"Score":"10"
},
{
"SurveyObjType":"2",
"Score":"10"
}
],
"SurveyReqID":"20191113XXXXXXXXX123456789"
}
1、取key的value值,值是字符串
SQL:
SELECT
cast(JSON_TEXT as json).jsonextractvalue('$.SurveyReqID') as SURVEY_REQ_ID
FROM
T_TABLE
;
结果:
20191113XXXXXXXXX123456789
2、取key的value值,值是数组中的值
SQL:
SELECT
cast(JSON_TEXT as json).jsonextractvalue('$.SurveyResult[0].SurveyObjType') as SURVEY_OBJ_TYPE
FROM
T_TABLE
;
结果: