使用PowerShell解析JSON-基于另一个值获取一个值

我正在执行一个接收JSON响应的API请求。响应看起来像这样（为了这个问题的目的而缩短）：

{
    "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"
        },

现在，在某些情况下，results.via.source.from name和address字段将为空，因为客户使用了表单，并且没有通过电子邮件发送。在这种情况下，自定义字段将包含我要的数据。例如：

{
            "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"
                },
             ]

如果via.source.from.name字段（或地址）为空，我希望脚本从相关的自定义字段中获取名字和姓氏以及地址。字段ID将始终相同。
数据被推送到SQL Server表中，代码如下：

$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
}

我已经尝试在if语句下使用以下代码来插入基于那些自定义字段的名称，但似乎都不起作用。

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
        }

上面的代码生成了一个包含所有custom_field值的连续行，而不仅仅是我指定的那些：

VIA== web
===      False 2023-04-13 ANPA      <redacted> True   True    703 Pacific Highway  <redacted>    <redacted> False    <redacted>    True   2280  <redacted>

用一种稍微不同的方法尝试一个名字

$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
        }

让我

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

这里最好的方法是什么？

你的示例代码有很多问题-而不是试图一个问题一个问题地挑出来，这里有一个有用的示例。如果你有关于为什么你的代码的部分 * 不 * 工作（或者至少不做你期望他们做的事情）的具体问题，那么最好是针对它们提出新的问题。
首先，让我们设置一些有代表性的测试数据。我们不需要完整API响应中的所有属性，所以我从你的示例json中删除了很多...

$json = @"
{
  "results": [
    {
      "via": {
        "source": {
          "from": {}
        }
      },
      "custom_fields": [
        {
          "id": 4495582893081,
          "value": "FirstName"
        },
        {
          "id": 4495561030297,
          "value": "Surname"
        },
      ]
    }
  ]
}
"@

$data = $json | ConvertFrom-Json;

接下来，处理数组中的每个结果。这可能需要一些额外的错误处理，例如，“name”和“custom_fields”都不包含请求者名称...

# 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;

}

如果我在本地运行这两个示例，我会得到这样的输出，这就是我认为你想要的：

FirstName

使用PowerShell解析JSON-基于另一个值获取一个值

1条答案

相关问题

热门标签

最新问答