如何将关联的data与data2的attrs字段合并导出为CSV？

需求说明 #

现有JSON数据包含data和data2两部分，需通过data.relations关联两者后导出为CSV，要求CSV包含所有data.attrs和data2.attrs的字段，而非仅data2.attrs字段。

原始JSON数据：

$str = '{
    "data": [
        { "attrs": { "id": "A", "more": "A" }, 
          "relations": [{"id": "r11"}, {"id": "r12"}] },
        { "attrs": { "id": "B", "more": "B" }, 
          "relations": [{"id": "r21"}] }
    ],
    "data2": [
        {"id": "r11", "attrs": { "x": "11", "y": "1"}},
        {"id": "r12", "attrs": { "x": "12", "y": "2"}},
        {"id": "r21", "attrs": { "x": "21", "y": "1"}}
    ]}'
$json = $str | ConvertFrom-Json

现有代码仅能导出data2.attrs字段，无法包含data.attrs的所有字段：

$json.data | 
% { 
    $_.relations.id | 
    % { 
        $json.data2 | ? id -eq $_ | select -ExpandProperty attrs # need to add data.attrs
    } 
} | 
ConvertTo-Csv

现有输出：

"x","y"
"11","1"
"12","2"
"21","1"

预期输出需包含所有data.attrs和data2.attrs字段：

"id","more","x","y"
"A","A","11","1"
"A","A","12","2"
"B","B","21","1"

解决方案 #

修改代码，动态合并data.attrs与对应data2.attrs的所有属性，无需硬编码字段名：

$json.data | ForEach-Object {
    $dataAttrs = $_.attrs
    # 遍历当前data项的所有关联ID
    foreach ($relId in $_.relations.id) {
        # 找到对应的data2项的attrs
        $data2Attrs = $json.data2 | Where-Object id -eq $relId | Select-Object -ExpandProperty attrs
        # 合并两个属性对象为一个新的自定义对象
        $combinedProps = [ordered]@{}
        # 先添加data.attrs的所有属性
        $dataAttrs.PSObject.Properties | ForEach-Object {
            $combinedProps[$_.Name] = $_.Value
        }
        # 再添加data2.attrs的所有属性
        $data2Attrs.PSObject.Properties | ForEach-Object {
            $combinedProps[$_.Name] = $_.Value
        }
        # 输出合并后的对象
        [PSCustomObject]$combinedProps
    }
} | ConvertTo-Csv -NoTypeInformation

代码说明 #

1. 遍历每个data项，先保存当前项的attrs到变量$dataAttrs
2. 对当前data项的每个关联ID，找到对应的data2项的attrs
3. 使用有序哈希表[ordered]@{}确保字段顺序与data.attrs在前、data2.attrs在后一致
4. 遍历两个attrs对象的所有属性，添加到哈希表中
5. 将哈希表转换为PSCustomObject，最后统一导出为CSV

执行后输出符合预期：

"id","more","x","y"
"A","A","11","1"
"A","A","12","2"
"B","B","21","1"

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