Parsing JSON with PowerShell – 根据另一个值获取一个值

huangapple
go评论58阅读模式
英文:

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

huangapple
  • 本文由 发表于 2023年4月17日 10:33:32
  • 转载请务必保留本文链接：https://go.coder-hub.com/76031374.html
  • arrays
  • json
  • powershell
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定