PowerShell提取NoteProperty对象并导出CSV及结构化访问

1. 导出指定格式的CSV文件 #

由于CSV无法直接存储嵌套对象，我们需要先将嵌套属性序列化为JSON字符串，再导出为CSV。以下代码会将每个用户（johndoe/johndoe2）转换为一行数据，匹配需求中的表头：

# 处理原始嵌套数据，转换为扁平对象数组
$processedPersons = $personData.persons[0].psobject.Properties | ForEach-Object {
    $userData = $_.Value[0]
    [PSCustomObject]@{
        id              = $userData.id
        family_address  = $userData.family_adress | ConvertTo-Json -Compress
        childrens       = $userData.childiren | ConvertTo-Json -Compress
        years_activitys = $userData.years_activity | ConvertTo-Json -Compress
    }
}

# 导出到CSV文件（路径可自行修改）
$processedPersons | Export-Csv -Path "./persons.csv" -NoTypeInformation -Encoding UTF8

2. 读取CSV并遍历打印（展开子数组） #

读取CSV时需要将JSON字符串反序列回对象，再遍历打印属性，对数组类型的years_activitys单独展开：

# 读取CSV并反序列化嵌套属性
$personsFromCsv = Import-Csv -Path "./persons.csv" | ForEach-Object {
    $_ | Select-Object `
        id,
        @{Name='family_address'; Expression={$_.family_address | ConvertFrom-Json}},
        @{Name='childrens'; Expression={$_.childrens | ConvertFrom-Json}},
        @{Name='years_activitys'; Expression={$_.years_activitys | ConvertFrom-Json}}
}

# 遍历打印每个用户的属性
foreach ($person in $personsFromCsv) {
    Write-Host "id: $($person.id)"
    Write-Host "family_address:"
    $person.family_address.psobject.Properties | ForEach-Object {
        Write-Host "  $($_.Name): $($_.Value)"
    }
    Write-Host "childrens:"
    $person.childrens.psobject.Properties | ForEach-Object {
        Write-Host "  $($_.Name): $($_.Value)"
    }
    Write-Host "years_activitys:"
    $person.years_activitys.psobject.Properties | ForEach-Object {
        Write-Host "  年份: $($_.Name)"
        Write-Host "    条目:"
        $_.Value | ForEach-Object { Write-Host "      - $_" }
    }
    Write-Host "-------------------------"
}

3. 结构化访问years_activitys的键值对 #

通过psobject.Properties可以直接遍历years_activitys的键值对，实现结构化访问。如果需要按用户名定位用户，可在导出CSV时添加username字段：

步骤1：导出时保留用户名 #

$processedPersons = $personData.persons[0].psobject.Properties | ForEach-Object {
    [PSCustomObject]@{
        username        = $_.Name  # 添加用户名字段
        id              = $_.Value[0].id
        family_address  = $_.Value[0].family_adress | ConvertTo-Json -Compress
        childrens       = $_.Value[0].childiren | ConvertTo-Json -Compress
        years_activitys = $_.Value[0].years_activity | ConvertTo-Json -Compress
    }
}
$processedPersons | Export-Csv -Path "./persons.csv" -NoTypeInformation -Encoding UTF8

步骤2：按用户名结构化访问 #

$personsFromCsv = Import-Csv "./persons.csv" | ForEach-Object {
    $_ | Select-Object `
        username,
        id,
        @{Name='family_address'; Expression={$_.family_address | ConvertFrom-Json}},
        @{Name='childrens'; Expression={$_.childrens | ConvertFrom-Json}},
        @{Name='years_activitys'; Expression={$_.years_activitys | ConvertFrom-Json}}
}

# 定位目标用户
$targetUsername = "johndoe"
$targetPerson = $personsFromCsv | Where-Object { $_.username -eq $targetUsername }

# 遍历years_activitys的键值对
foreach ($activity in $targetPerson.years_activitys.psobject.Properties) {
    Write-Host "key: $($activity.Name)"
    Write-Host "value:"
    $activity.Value | ForEach-Object { Write-Host "  - $_" }
}

内容的提问来源于stack exchange，提问作者Ichigo Kurosaki