英文:
Parsing JSON with Powershell - get a value based on another value
问题
It looks like you're trying to extract data from JSON responses and handle cases where specific fields are empty. In your PowerShell script, you can use the following approach to handle the custom fields when the via.source.from.name field is empty:
ForEach ($test in $test1) {
If ($test.via.source.from.name -eq $Null) {
$firstnameField = $test.custom_fields | Where-Object { $_.id -eq 4495582893081 }
$lastnameField = $test.custom_fields | Where-Object { $_.id -eq 4495561030297 }
$requester_name = $firstnameField.value + " " + $lastnameField.value
} Else {
$requester_name = $test.via.source.from.name
}
# Rest of your code to process $requester_name and other fields
}
This code first checks if via.source.from.name is empty. If it is, it searches for the custom fields with the specified IDs (4495582893081 and 4495561030297) and extracts their values to construct the requester_name. If via.source.from.name is not empty, it uses that value.
This should help you correctly handle the scenario where the name fields come from custom fields when via.source.from.name is empty.
英文:
I am executing an API request that receives a JSON response. The response looks like this (shortened for the purpose of this question):
{
"results": [
{
"url": "https://domain.zendesk.com/api/v2/tickets/12345.json",
"id": 12345,
"external_id": null,
"via": {
"channel": "email",
"source": {
"from": {
"address": "person@yahoo.com.au",
"name": "Person Name"
},
"to": {
"name": "Company",
"address": "info@company.com.au"
},
"rel": null
}
},
"created_at": "2023-04-17T00:08:14Z",
"updated_at": "2023-04-17T00:08:14Z",
"type": null,
"subject": "Re: Dispatch Advice",
"raw_subject": "Re: Dispatch Advice",
"description": "email body",
"priority": "normal",
"status": "open",
"recipient": "info@company.com.au",
"requester_id": 17600210801689,
"submitter_id": 17600210801689,
"assignee_id": null,
"organization_id": null,
"group_id": 4480945892505,
"collaborator_ids": [],
"follower_ids": [],
"email_cc_ids": [],
"forum_topic_id": null,
"problem_id": null,
"has_incidents": false,
"is_public": true,
"due_at": null,
"tags": [],
"custom_fields": [
{
"id": 4481790575897,
"value": null
},
{
"id": 10936520871705,
"value": null
},
{
"id": 5536560189977,
"value": null
}
],
"satisfaction_rating": {
"score": "unoffered"
},
"sharing_agreement_ids": [],
"custom_status_id": 4480835200921,
"fields": [
{
"id": 4481790575897,
"value": null
},
{
"id": 10936520871705,
"value": null
},
{
"id": 5536560189977,
"value": null
}
],
"followup_ids": [],
"ticket_form_id": 4480835198361,
"brand_id": 4480835199385,
"allow_channelback": false,
"allow_attachments": true,
"from_messaging_channel": false,
"result_type": "ticket"
},
Now, in some cases, the results.via.source.from name and address fields will be blank as the customer has used a form and not emailed in. In this event, the custom fields will contain the data I am after. For example:
{
"url": "https://domain.zendesk.com/api/v2/tickets/36765.json",
"id": 36765,
"external_id": null,
"via": {
"channel": "web",
"source": {
"from": {},
"to": {},
"rel": null
}
},
"subject": "Results enquiry - Name",
"status": "open",
"custom_fields": [
{
"id": 4495582893081,
"value": "FirstName"
},
{
"id": 4495561030297,
"value": "Surname"
},
]
If the via.source.from.name field (or address) is blank, I want the script to get the firstname and lastname plus address from the relevant custom fields. The field IDs will always be the same.
The data is pushed into an SQL Server table, here is the code:
$headers=@{}
$headers.Add("accept", "application/json")
$headers.Add("Authorization", "redacted")
$getTkts = Invoke-WebRequest -Method 'GET' -Uri "https://domain.zendesk.com/api/v2/search.json?query=created>48hours type:ticket" -Headers $headers
$tktInfo = $getTkts | ConvertFrom-JSON
$test1 = $tktInfo.results
ForEach ($test in $test1) {
If ($test.via.source.from.name -eq $Null) {
$requester_name = "INSERT HERE"
} Else {
$requester_name = $test.via.source.from.name
}
$sqlData = [PSCustomObject]@{
ticket_no = $test.id
submitted = $test.created_at
feedback_rating = $test.satisfaction_rating.score
feedback_reason = $test.satisfaction_rating.reason
feedback_comment = $test.satisfaction_rating.comment
requester_name = $requester_name
requester_email = $test.via.source.from.address
received_at = $test.via.source.to.address
form_used = $test.ticket_form_id
channel = $test.via.channel
}
Write-SqlTableData -ServerInstance "NP-SQL07" -DatabaseName "Test" -SchemaName "dbo" -TableName "Table_2" -InputData $sqlData
}
I have tried using the following under the if statement to insert the name based on those custom fields but none of it seems to work.
ForEach ($test in $test1) {
If ($test.via.source.from.name -eq $Null) {
$checkData = [PSCustomObject]@{
firstname = $test.custom_fields.value | Where-Object {$test.custom_fields.id -match "4495582893081"}
lastname = $test.custom.fields.value | Where-Object {$test.custom_fields.id -match "4495582893081"}
}
$requester_name = $checkData.firstname + " " + $checkData.lastname
Write-Host "VIA==" $test.via.channel
Write-Host "=== $requester_name ==="
# $check2.value | where $check2.id -eq '4481808286233'
#$test1.custom_fields.id, $test1.custom_fields.value | Where-Object {$test1.custom_fields.id -eq "4481808286233"}
} Else {
$requester_name = $test.via.source.from.name
}
the above produces a continuous line of all custom_field values instead of only those I have specified:
VIA== web
=== False 2023-04-13 ANPA <redacted> True True 703 Pacific Highway <redacted> <redacted> False <redacted> True 2280 <redacted>
and trying just one name using a slightly different method
$tktInfo = $getTkts | ConvertFrom-JSON
$test1 = $tktInfo.results
ForEach ($test in $test1) {
$check2 = $test1.custom_fields | Format-Table
If ($test.via.source.from.name -eq $Null) {
$requester_name = $check2.value | Where-Object $check2.id -match "4495582893081"
} Else {
$requester_name = $test.via.source.from.name
}
gets me
Where-Object : Cannot validate argument on parameter 'Property'. The argument is null or empty. Provide an argument that is not null or empty,
and then try the command again.
At I:\Information Technology\Scripts and Tools\Pull_Zendesk_Tkt_Stats.ps1:23 char:60
+ ... $requester_name = $check2.value | Where-Object $check2.id -match ...
+ ~~~~~~~~~~
+ CategoryInfo : InvalidData: (:) [Where-Object], ParameterBindingValidationException
+ FullyQualifiedErrorId : ParameterArgumentValidationError,Microsoft.PowerShell.Commands.WhereObjectCommand
What is the best approach here?
答案1
得分: 1
以下是翻译好的部分：
你的示例代码存在许多问题，而不是逐个问题进行分析，这里提供一个有效的示例。如果你对为什么你的代码的某些部分不起作用（或者至少不按照你的期望工作）有具体问题，最好是针对这些问题提出新的问题...
首先，让我们设置一些代表性的测试数据。我们不需要完整 API 响应中的所有属性，所以我已经从你的示例 JSON 中删除了很多属性...
接下来，处理数组中的每个结果。这可能需要一些额外的错误处理，以处理例如既不在 "name" 也不在 "custom_fields" 中包含请求者名称的情况...
$json = @"
{
"results": [
{
"via": {
"source": {
"from": {}
}
},
"custom_fields": [
{
"id": 4495582893081,
"value": "FirstName"
},
{
"id": 4495561030297,
"value": "Surname"
},
]
}
]
}
"@
$data = $json | ConvertFrom-Json;
然后，处理数组中的每个结果。这可能需要一些额外的错误处理，以处理例如既不在 "name" 也不在 "custom_fields" 中包含请求者名称的情况...
# 循环遍历 "results" 数组中的每个项目
foreach( $result in $data.results )
{
# 尝试从默认位置读取请求者名称
$requester_name = $result.via.source.from.name;
# 如果我们在默认位置找不到请求者名称
# 我们会在 "custom_fields" 中查找
if( $null -eq $requester_name )
{
# 找到 "custom_fields" 数组中具有 id 4495582893081 的条目，
# 然后获取其 "value" 属性
$requester_name = ($result.custom_fields | where-object { $_.id -eq 4495582893081 }).value;
}
# 对请求者名称执行某些操作
# (在这个示例中，我们只会将其记录到控制台)
write-host $requester_name;
}
如果我在本地运行这两个示例，我会得到以下输出，这应该是你想要的：
FirstName
英文:
Your sample code has a number of problems - rather than try to pick it apart issue by issue, here's a sample that works. If you have specific questions about why parts of your code don't work (or at least don't do what you expect them to do) it might be better to open new questions about them specifically...
First, let's set up some representative test data. We don't need all the properties from the full api response so I've removed a lot of them from your sample json...
$json = @"
{
"results": [
{
"via": {
"source": {
"from": {}
}
},
"custom_fields": [
{
"id": 4495582893081,
"value": "FirstName"
},
{
"id": 4495561030297,
"value": "Surname"
},
]
}
]
}
"@
$data = $json | ConvertFrom-Json;
Next, process each result in the array. This probably needs some additional error handling for cases where, for example, neither the "name" nor "custom_fields" contain a requester name...
# loop through each item in the "results" array
foreach( $result in $data.results )
{
# try to read the requester name from the default location
$requester_name = $result.via.source.from.name;
# if we didn't find the requester name in the default location
# we look in the "custom_fields" instead
if( $null -eq $requester_name )
{
# find the entry in the "custom_fields" array that has the id 4495582893081,
# and then get its "value" property
$requester_name = ($result.custom_fields | where-object { $_.id -eq 4495582893081 }).value;
}
# do something with the requester name
# (we'll just log it to the console for this example)
write-host $requester_name;
}
If I run these two samples locally I get this output, which is what I think you're after:
FirstName
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论