I have retrieved json log data from a rest API as follows

[
  {
    "id": "6523276",
    "type": "logs",
    "attributes": {
        "created-at": "2022-02-22T10:50:26Z",
        "action": "delete",
        "resource-name": "DocumentABC.docx",
        "user-name": "Joe Smith"
        }
  },
  {
    "id": "6523275",
    "type": "logs",
    "attributes": {
        "created-at": "2022-02-22T10:03:22Z",
        "action": "create",
        "resource-name": "Document123.docx",
        "user-name": "Joe Smith"
    }
  },
  {
    "id": "6523274",
    "type": "logs",
    "attributes": {
        "created-at": "2022-02-22T06:42:21Z",
        "action": "open",
        "resource-name": "123Document.docx",
        "user-name": "Joe Smith"
    }
  }
]

I need to Post the json to another web app but I only want the last hour of logs. In the json example above, the current time was 2022-02-22T10:55:22Z, therefore I'm only interested in the first two log entries.

例如

[
  {
    "id": "6523276",
    "type": "logs",
    "attributes": {
        "created-at": "2022-02-22T10:50:26Z",
        "action": "delete",
        "resource-name": "DocumentABC.docx",
        "user-name": "Joe Smith"
        }
  },
  {
    "id": "6523275",
    "type": "logs",
    "attributes": {
        "created-at": "2022-02-22T10:03:22Z",
        "action": "create",
        "resource-name": "Document123.docx",
        "user-name": "Joe Smith"
    }
  }
]

这是我的powershell v7脚本

$json = $json | ConvertFrom-Json
$filterTime = (Get-date).AddHours(-1)
$RFCfilterTime = [Xml.XmlConvert]::ToString($filterTime,[Xml.XmlDateTimeSerializationMode]::Utc)
$Filteredjson = $json | Where-Object $json.attributes[0] -ge $RFCfilterTimefilterDate  
$jsonToPost = ConvertTo-Json -InputObject @($Filteredjson) -Depth 5

问题是ConvertFrom-Json将‘Created-at’从RFC3339格式更改为‘DateTime’格式. 因此,WHERE-OBJECT过滤不起作用.

id                 type attributes
--                 ---- ----------
6523276            logs @{created-at=22/02/2022 10:50:26 AM; action…
6523275            logs @{created-at=22/02/2022 10:03:22 AM; action…
6523274            logs @{created-at=22/02/2022 6:42:21 AM; action=…

如何将所有"Created-at"对象改回RCF3339格式?

Is the $json | Where-Object $json.attributes[0] -ge $RFCfilterTimefilterDate
statement being used correctly?

Is there any easier way altogether?

推荐答案

Your approach should work in principle, but there was a problem with your Where-Object statement - see the bottom section.
Mathias' answer shows how to work with the [datetime] instances that result from ConvertTo-Json's parsing directly, but a bit more work is required:

  • 事实上,in PowerShell (Core) v6+ 100(与JSON web服务一起被Invoke-RestMethod使用implicitly)automatically deserializes ISO 8601-format date-time strings such as 104 into 105 101 instances,以及相反地,在(重新)序列化ConvertTo-Json[datetime]个实例时,被(重新)转换为ISO 8601字符串.

  • While this enables convenient chronological comparisons with other [datetime] instances, such as returned by Get-Date, there is a major pitfall: Only [datetime] instances that have the same .Kind property value compare meaningfully (possible values are Local, Utc, and Unspecified, the latter being treated like Local in comparisons).

  • 不幸的是,从PowerShell 7.2.1开始,you don't get to control what 100 of 101 instances 102 constructs - it is implied by the specific date-time string format of each string被识别为ISO 8601日期.

    • Similarly, on (re)serialization with ConvertTo-Json, the .Kind value determines the string format.
    • 详情见this answer.

在您的例子中,因为日期时间字符串有Z个后缀表示UTC,所以构造了[datetime]个实例和.KindUtc.

Therefore, you need to ensure that your comparison timestamp is a Utc [datetime] too, which calling .ToUniversalTime() on the Local instance that Get-Date outputs ensures:

# Note the need for .ToUniversalTime()
$filterTime = (Get-Date).ToUniversalTime().AddHours(-1)
# Note: Only works as intended if all date-time strings are "Z"-suffixed
$filteredData = $data | Where-Object { $_.attributes.'created-at' -ge $filterTime }

However, at least hypothetically a given JSON document may contain differing date-time string formats that result in different .Kind values.

The way to handle this case - as well as the case where the string format is consistent, but not necessarily known ahead of time - you can use the generally preferable [datetimeoffset] (System.DateTimeOffset) type, which automatically recognizes timestamps as equivalent even if their expression (local vs. UTC) is different:

# Note the use of [datetimeoffset]
[datetimeoffset] $filterTime = (Get-Date).AddHours(-1)
# With this approach the specific format of the date-time strings is irrelevant,
# as long as they're recognized as ISO 8601 strings.
$filteredData = $data | 
  Where-Object { [datetimeoffset] $_.attributes.'created-at' -ge $filterTime }

注:严格地说,比较的第LHS个是[datetimeoffset]型就足够了-然后也正确地处理了[datetime]RHS.

Potential future improvements:

  • GitHub issue #13598建议在ConvertFrom-Json上增加-DateTimeKind个参数,以便允许显式地请求感兴趣的种类,并且可替换地构造[datetimeoffset]个实例.

As for what you tried:

$json | Where-Object $json.attributes[0] -ge $RFCfilterTimefilterDate号吗? 语句是否正确使用?

No:

  • 您使用的是simplified syntax,其中比较的LHS(-Property参数)必须是每个输入对象上直接可用的single(非嵌套)属性的名称

  • 因为在您的情况下需要nested属性访问,所以必须使用基于script-block的常规语法({ ... }),在这种情况下,必须通过automatic $_ variable显式引用手头的输入对象.

  • .attributes[0]表示您试图访问created-at属性by index,但PowerShell不支持该属性;你需要:

    • 或者:拼写出属性的名称,如果已知的话:$_.attributes.'created-at'-注意在这种情况下需要使用quote,因为使用了非标准的-字符.以我的名义

    • or: use the intrinsic .psobject member that provides reflection information about any given object: $_.attributes.psobject.Properties.Value[0]

Thus, with spelling out the property name - and with making sure that the LHS [datetime] value is represented as an ISO 8601-formatted string too, via .ToString('o') - your statement should have been:

$json | Where-Object {
  $_.attributes.'created-at'.ToString('o') -ge $RFCfilterTimefilterDate 
}

Json相关问答推荐

Swift解码错误类型与`Bool`type不一致

服务器不返回JSON

PowerShell:将Invoke-WebRequest与变量一起使用

GO KaZaam转换返回意外结果

数据到jsonObject到数据到 struct 是可能的吗?

将pyspark.sql.Rowtype数据转换为Json字符串,消除Azure Databricks NB中的值

取消嵌套数组并将数组名称添加为附加字段

如何将 JSON 文本作为参数传递给 powershell?

将 colly 包输出文本添加到 golang 中的映射

嵌套 JSON 到 CSV(多级)

如何在 wso2 中组合 2 个 json 有效负载?

Rails 控制器无需编码即可渲染 json

SyntaxError:Object.parse(本机)AngularJS中的意外标记o

如何在 Eclipse 中安装和使用 JSON 编辑器?

编写 JSON 日志(log)文件的格式?

alert Json 对象

使用 Node.js 对 JSON 中的字符串大小有限制吗?

区分字符串和列表的 Pythonic 方式是什么?

PostgreSQL 中的 JSON 模式验证?

如何将单引号转义成双引号转成单引号