mysql 字段为json格式,返回各个字段
程序员文章站
2022-05-31 19:26:28
...
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;