用数组展平嵌套的 JSON 并过滤到 CSV [英] Flatten a nested JSON with array and filter to CSV
问题描述
我有一个问题如何使用 Powershell 将嵌套的 JSON 展平并转换为 CSV.下面是我的 JSON,这是从 Office 365 获取的邮件消息日志,其中包含许多用户消息,我需要过滤列、展平并转换为 CSV:
I got a question about How to use Powershell flatten a nested JSON and covert to CSV. Below is my JSON, which is a mail message log get from Office 365 with many users messages, I need to filter the columns, flatten and convert to CSV :
createdDateTime,
receivedDateTime,
from_name
from_adress
To_name_1
To_adress_2
To_name_2
To_adress_2
...
来自"字段只有一个数据.但是toRecipients"是一个数组.
The "from" field has only one data. But the "toRecipients" is a array.
{
...
"createdDateTime": "xxxx-xx-xx",
"receivedDateTime": "xxxx-xx-xx",
"isRead": true,
"from": {
"emailAddress": {
"name": "John",
"adress": "john@onmicrosoftware.com"
}
},
"toRecipients": [
{
"emailAddress": {
"name": "Amy",
"adress": "Amy@onmicrosoftware.com"
}
},
{
"emailAddress": {
"name": "Amy",
"adress": "Amy@onmicrosoftware.com"
}
}
]
}
推荐答案
这是一个完整的可运行示例.它将创建一个文件C:\test.csv".
Here is a complete runnable example. It will create a file "C:\test.csv".
没有将嵌套对象展平为平面对象的自动"方式.但是您可以手动为平面对象创建分配属性.
There is no "automatic" way of flattening a nested object to a flat object. But you can manually create assign properties to a flat object.
首先我将 JSON 文本解析为一个 powershell 对象
First I parse the JSON text into a powershell object
$obj = @"
{
"createdDateTime": "xxxx-xx-xx",
"receivedDateTime": "xxxx-xx-xx",
"isRead": true,
"from": {
"emailAddress": {
"name": "John",
"adress": "john@onmicrosoftware.com"
}
},
"toRecipients": [
{
"emailAddress": {
"name": "Amy",
"adress": "Amy@onmicrosoftware.com"
}
},
{
"emailAddress": {
"name": "Amy",
"adress": "Amy@onmicrosoftware.com"
}
}
]
}
"@ | ConvertFrom-Json
现在获取 Powershell 对象(或对象列表,即使您有很多这样的条目,这也能工作)并将其通过管道传输到 ForEach-Object.在循环内部,将不同的属性映射到一个平面对象.
Now take the Powershell object (or list of objects, this will work even if you have many of these entries) and pipe it to ForEach-Object. Inside the loop map the different properties to a flat object.
$flattened = $obj | ForEach-Object {
return [PSCustomObject]@{
createdDateTime = $_.createdDateTime
receivedDateTime = $_.receivedDateTime
from_name = $_.from.emailAddress.name
from_adress = $_.from.emailAddress.adress
to_name_1 = $_.toRecipients[0].emailAddress.name
to_adress_1 = $_.toRecipients[0].emailAddress.adress
to_name_2 = $_.toRecipients[1].emailAddress.name
to_adress_2 = $_.toRecipients[1].emailAddress.adress
}
}
现在您可以将整个内容导出为 CSV
Now you can export the entire thing as a CSV
$flattened | Export-Csv C:\test.csv -Delimiter ";" -Encoding UTF8
这里假设总是有 2 个 toRecipients.如果遇到更多的情况,可以动态添加 to_name_3、to_name_4 等,但这要复杂一些.
This assumes that there will always be 2 toRecipients. It would be possible to dynamically add to_name_3, to_name_4, and so on if more are encountered, but that's quite a bit more complicated.
这篇关于用数组展平嵌套的 JSON 并过滤到 CSV的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!