gpt4 book ai didi

arrays - Powershell 嵌套 JSON 到 csv 转换

转载 作者:行者123 更新时间:2023-12-05 03:33:41 26 4
gpt4 key购买 nike

我有一个相当特殊的嵌套 JSON,在某些情况下,键值对正常出现,但在其他情况下,键的类型出现在进一步的嵌套中。

{"metadata":{"systemId":"da1895","legalEntity":"A0"},"recordContent":{"positionDate":"2019-04-08 00:00:00.0","account":{"string":"G32"},"seg":{"string":"S"},"strike":{"double":4.4}}}
{"metadata":{"systemId":"45364d","legalEntity":"5G"},"recordContent":{"positionDate":"2019-04-08 00:00:00.0","account":{"string":"G81"},"seg":{"string":"S"},"strike":{"double":5.0}}}

在示例中,您可以看到 metadata 的字段是简单的键值对,但在 recordContent 下,我们有 positionDate,它是一个简单的键值,但 "account":{"string":"G32"}"strike":{"double":4.4} 不是。

我想放弃类型信息并得到如下 CSV 结构:

systemId, legalEntity, positionDate,         account,seg,strike
da1895, A0, 2019-04-08 00:00:00.0,G32, S, 4.4
4536d, 5G, 2019-04-08 00:00:00.0,G81, S, 5.0

关于如何使用 Powershell 将此类结构转换为 CSV 的任何想法?

这是我尝试过的:

$TemplateParametersFile = "c:\data\output.json"
$JsonParameters = Get-Content $TemplateParametersFile | ConvertFrom-Json

$metadatafields = $JsonParameters.metadata[0].PSObject.Properties.Name
$recordcontentfields = $JsonParameters.recordContent[0].PsObject.Properties.Name

$oData = New-Object PSObject

$metadatafields |
ForEach {
Add-Member -InputObject $oData -NotePropertyName ($_) -NotePropertyValue $JsonParameters.metadata.($_)
}

$recordcontentfields |
ForEach {
Add-Member -InputObject $oData -NotePropertyName ($_) -NotePropertyValue $JsonParameters.recordContent.($_)
}

这给了我:

$oData

systemId : {da1895, 45364d}
legalEntity : {A0, 5G}
positionDate : {2019-04-08 00:00:00.0, 2019-04-08 00:00:00.0}
account : {@{string=G32}, @{string=G81}}
seg : {@{string=S}, @{string=S}}
strike : {@{double=4.4}, @{double=5.0}}

我现在有点卡住了,上面没有转换为 csv。

请注意,除了元数据和 recordContent 之外,我没有对任何字段名进行硬编码,我希望在 JSON 结构发生变化时保持这种灵 active 。

谢谢

最佳答案

我建议以有序hashtable 的方式迭代收集属性-名称-值对([ordered] @{}),然后可以将其转换为 [pscustomobject] 以将其转换为自定义对象。

以下解决方案中没有属性名称 是硬编码的,但是对象图结构 假定遵循示例 JSON 中的模式,该模式仅限于一个嵌套级别 - 如果您需要处理任意嵌套的对象,this answer可能是一个起点。

反射(属性名称和值的发现)是通过 intrinsic .psobject property 执行的PowerShell 使所有对象都可用。

# Parse sample JSON into an array of [pscustomobject] graphs.
$fromJson = ConvertFrom-Json @'
[
{"metadata":{"systemId":"da1895","legalEntity":"A0"},"recordContent":{"positionDate":"2019-04-08 00:00:00.0","account":{"string":"G32"},"seg":{"string":"S"},"strike":{"double":4.4}}}
,
{"metadata":{"systemId":"45364d","legalEntity":"5G"},"recordContent":{"positionDate":"2019-04-08 00:00:00.0","account":{"string":"G81"},"seg":{"string":"S"},"strike":{"double":5.0}}}
]
'@

# Initialize an aux. ordered hashtable to collect the property-name-value
# pairs in.
$oht = [ordered] @{}

$fromJson | ForEach-Object {
$oht.Clear()
# Loop over top-level properties.
foreach ($topLevelProp in $_.psobject.Properties) {
# Loop over second-level properties.
foreach ($prop in $topLevelProp.Value.psobject.Properties) {
if ($prop.Value -is [System.Management.Automation.PSCustomObject]) {
# A nested value: Use the value of the (presumed to be one-and-only)
# property of the object stored in the value.
$oht[$prop.Name] = $prop.Value.psobject.Properties.Value
}
else {
# A non-nested value: use as-is.
$oht[$prop.Name] = $prop.Value
}
}
}
# Construct and output a [pscustomobject] from the aux. ordered hashtble.
[pscustomobject] $oht
} |
ConvertTo-Csv # Replace this with Export-Csv to export to a file.

以上结果:

"systemId","legalEntity","positionDate","account","seg","strike"
"da1895","A0","2019-04-08 00:00:00.0","G32","S","4.4"
"45364d","5G","2019-04-08 00:00:00.0","G81","S","5"

关于arrays - Powershell 嵌套 JSON 到 csv 转换,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/70308198/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com