twilio - 处理来自 Twilio 的 json 结果
问题描述
我目前从 Twilio 检索语音和短信警报作为 json 列。使用这个 tsql 代码,
SELECT [ReceivedDateTime]
,MsgSid = JSON_VALUE(payload, '$.resource_sid')
,WebhookURL = JSON_VALUE(payload, '$.webhook.request.url')
,ToState = JSON_VALUE(payload, '$.webhook.request.parameters.ToState')
,AlertTime = JSON_VALUE(payload, '$.webhook.request.parameters.Timestamp')
,Direction = JSON_VALUE(payload, '$.webhook.request.parameters.Direction')
,SequenceNumber = JSON_VALUE(payload, '$.webhook.request.parameters.SequenceNumber')
,ToNumber = JSON_VALUE(payload, '$.webhook.request.parameters.To')
,ToCity = JSON_VALUE(payload, '$.webhook.request.parameters.ToCity')
,CallStatus = JSON_VALUE(payload, '$.webhook.request.parameters.CallStatus')
,FromNumber = JSON_VALUE(payload, '$.webhook.request.parameters.From')
,AccountSid = JSON_VALUE(payload, '$.webhook.request.parameters.AccountSid')
,TwilioReason = JSON_VALUE(payload, '$.response.headers.X-Twilio-Reason')
,AlertDate = JSON_VALUE(payload, '$.webhook.response.headers.Date')
FROM [dbo].[TwilioAlerts]
我可以查询这个文件,直到我到达名称中包含破折号的部分。尝试引入 X-Twilio-Reason,我收到错误:JSON 路径格式不正确。在第 20 位发现意外字符“-”。如何在 tsql 中引用它?
这是我试图查询的 json:
{
"resource_sid": "",
"service_sid": null,
"error_code": "15003",
"more_info": {
"Msg": "HTTP retrieval failure",
"statusCallback": "",
"ErrorCode": "15003",
"LogLevel": "WARN"
},
"webhook": {
"type": "application/json",
"request": {
"url": "",
"method": "POST",
"headers": {},
"parameters": {
"Called": "+",
"ToState": "TX",
"CallerCountry": "US",
"Timestamp": "Fri, 22 Nov 2019 14:33:23 +0000",
"Direction": "outbound-api",
"CallbackSource": "call-progress-events",
"CallerState": "TX",
"ToZip": "78742",
"SequenceNumber": "1",
"CallSid": "",
"To": "+",
"CallerZip": "",
"ToCountry": "US",
"CalledZip": "",
"ApiVersion": "2010-04-01",
"CalledCity": "",
"CallStatus": "ringing",
"From": "+",
"AccountSid": "",
"CalledCountry": "US",
"CallerCity": "",
"ToCity": "AUSTIN",
"Caller": "+",
"FromCountry": "US",
"FromCity": "",
"CalledState": "TX",
"FromZip": "",
"FromState": "TX"
}
},
"response": {
"status_code": null,
"headers": {
"X-Twilio-WebhookAttempt": "1",
"X-Twilio-Reason": "Response does not contain content type",
"Content-Length": "464",
"Date": "Fri, 22 Nov 2019 14:33:23 GMT",
"Content-Type": "text/html"
},
"body":"Twilio was unable to fetch content from: https://appointmentwave.com/ProdAWHandler/events/voice\nError: Error reading response: Response does not contain content type\nAccount SID: ACc9eea08b61d774d5dfc07c91aa690466\nSID: CA6e2b878c4e1e5d2ec3733d52b2c2b2fa\nRequest ID: 6ae4e44b-f6cb-4ca3-a057-6067024f943a\nRemote Host: appointmentwave.com\nRequest Method: POST\nRequest URI: https://appointmentwave.com/ProdAWHandler/events/voice\nSSL
Version: TLSv1.2\nURL Fragment: true"}}}
关于如何拉“X-Twilio-Reason”的任何答案?
解决方案
尝试在名称中带有破折号的部分周围添加双引号。
,TwilioReason = JSON_VALUE(payload, '$.response.headers."X-Twilio-Reason"')
推荐阅读
- c# - 如何获取通过引用传递的变量名字符串?
- reactjs - 从 useEffect 访问上下文
- apama - 来自 Cumulocity 中内置 Apama 运行时的 HTTP 请求
- c++ - 运行代码时访问冲突写入位置0x00000079
- java - Intellij-idea 类导入
- java - 使用特定区域设置/国家/地区的可读类型格式化日期
- html - 锚点在不同页面上的行为不同
- java - 基于查询参数动态改变Zuul中路由的url
- shell - lftp 将本地内容目录镜像到远程
- java - 向 C++ Java Enum 模拟器添加 c++17 constexpr 和其他改进