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

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;

分享到：

2018-11-29 15:43

浏览 1799

评论