mysql 字段为json格式，返回各个字段 -

冷h锋

浏览: 25259 次
性别:
来自: 青岛

最近访客更多访客>>

yangjiaqiao1006

Ivan_Pig

James103

wz595702583

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

mysql 字段为json格式，返回各个字段

博客分类：

mysql

json mysql


SELECT
	id,
	callId,
	JSON_UNQUOTE ( json_extract ( messageJson, '$.type' ) ) AS type,
	JSON_UNQUOTE ( json_extract ( messageJson, '$.callType' ) ) AS callType,
	JSON_UNQUOTE ( json_extract ( messageJson, '$.appSid' ) ) AS appSid,
	JSON_UNQUOTE ( json_extract ( messageJson, '$.userNum' ) ) AS userNum,
	JSON_UNQUOTE ( json_extract ( messageJson, '$.agentNo' ) ) AS agentNo,
	JSON_UNQUOTE ( json_extract ( messageJson, '$.agentNum' ) ) AS agentNum,
	JSON_UNQUOTE ( json_extract ( messageJson, '$.userDisplayNum' ) ) AS userDisplayNum,
	JSON_UNQUOTE ( json_extract ( messageJson, '$.agentDisplayNum' ) ) AS agentDisplayNum,
	JSON_UNQUOTE ( json_extract ( messageJson, '$.startTime' ) ) AS startTime,
	JSON_UNQUOTE ( json_extract ( messageJson, '$.endTime' ) ) AS endTime,
	JSON_UNQUOTE ( json_extract ( messageJson, '$.userStartCallTime' ) ) AS userStartCallTime,
	JSON_UNQUOTE ( json_extract ( messageJson, '$.userRingTime' ) ) AS userRingTime,
	JSON_UNQUOTE ( json_extract ( messageJson, '$.userAnswerTime' ) ) AS userAnswerTime,
	JSON_UNQUOTE ( json_extract ( messageJson, '$.agentStartCallTime' ) ) AS agentStartCallTime,
	JSON_UNQUOTE ( json_extract ( messageJson, '$.agentRingTime' ) ) AS agentRingTime,
	JSON_UNQUOTE ( json_extract ( messageJson, '$.agentAnswerTime' ) ) AS agentAnswerTime,
	JSON_UNQUOTE ( json_extract ( messageJson, '$.agentEndTime' ) ) AS agentEndTime,
	JSON_UNQUOTE ( json_extract ( messageJson, '$.recordUrl' ) ) AS recordUrl,
	JSON_UNQUOTE ( json_extract ( messageJson, '$.relatedCallId' ) ) AS relatedCallId,
	JSON_UNQUOTE ( json_extract ( messageJson, '$.surveyInput' ) ) AS surveyInput,
	JSON_UNQUOTE ( json_extract ( messageJson, '$.callResult' ) ) AS callResult,
	JSON_UNQUOTE ( json_extract ( messageJson, '$.userArea' ) ) AS userArea,
	JSON_UNQUOTE ( json_extract ( messageJson, '$.userCallState' ) ) AS userCallState,
	JSON_UNQUOTE ( json_extract ( messageJson, '$.agentCallState' ) ) AS agentCallState,
	JSON_UNQUOTE ( json_extract ( messageJson, '$.applicationData' ) ) AS applicationData,
	JSON_UNQUOTE ( json_extract ( messageJson, '$.userData' ) ) AS userData,
	JSON_UNQUOTE ( json_extract ( messageJson, '$.queueingTime' ) ) AS queueingTime,
	JSON_UNQUOTE ( json_extract ( messageJson, '$.queueSid' ) ) AS queueSid,
	JSON_UNQUOTE ( json_extract ( messageJson, '$.agentGroupSid' ) ) AS agentGroupSid,
	JSON_UNQUOTE ( json_extract ( messageJson, '$.ext' ) ) AS ext ,
	isSync
FROM
	`cc_cdr` 
WHERE
	dataType = 'cdr'
	
	ORDER BY id desc;

分享到：