将嵌套的 JSON 数组转换为 CSV 文件中的单独列 [英] Convert nested JSON array into separate columns in CSV file

问题描述

我有一个如下所示的 JSON 文件:

I have a JSON file that looks like this:

{
    "id": 10011,
    "title": "Test procedure",
    "slug": "slug",
    "url": "http://test.test",
    "email": "[e-mail]",
    "link": "http://test.er",
    "subject": "testing",
    "level": 1,
    "disciplines": [
      "discipline_a",
      "discipline_b",
      "discipline_c"
    ],
    "areas": [
      "area_a",
      "area_b"
    ]
  },

我试图使用以下命令将其转换为 CSV 文件:

I was trying to use the following command to convert that into the CSV file:

(Get-Content "PATH_TO	est.json" -Raw | ConvertFrom-Json)| Convertto-CSV -NoTypeInformation | Set-Content "PATH_TO	est.csv"

但是，对于学科和领域，我在生成的 CSV 文件中获取 System.Object[].

However, for disciplines and areas I am getting System.Object[] in the resulting CSV file.

有没有办法将所有这些嵌套值作为单独的列放在 CSV 文件中，例如 area_1、area_2 等.对于学科也是如此.

Is there a way to put all those nested values as a separate columns in CSV file like area_1, area_2 etc. And the same for disciplines.

推荐答案

CSV 转换/导出 cmdlet 无法展平"对象，我可能会遗漏一些东西，但我不知道有什么办法可以用内置 cmdlet 或功能.如果您可以保证 disciplines 和 areas 将始终具有相同数量的元素，则可以通过使用带有派生属性的 Select-Object 来简化它这样做:

The CSV conversion/export cmdlets have no way of "flattening" an object, and I may be missing something, but I know of no way to do this with a built-in cmdlet or feature. If you can guarantee that disciplines and areas will always have the same number of elements, you can trivialize it by using Select-Object with derived properties to do this:

$properties=@('id','title','slug','url','email','link','subject','level',
    @{Name='discipline_1';Expression={$_.disciplines[0]}}
    @{Name='discipline_2';Expression={$_.disciplines[1]}}
    @{Name='discipline_3';Expression={$_.disciplines[2]}}
    @{Name='area_1';Expression={$_.areas[0]}}
    @{Name='area_2';Expression={$_.areas[1]}}
)
(Get-Content 'PATH_TO	est.json' -Raw | ConvertFrom-Json)| Select-Object -Property $properties | Export-CSV -NoTypeInformation -Path 'PATH_TO	est.csv'

但是，我假设 disciplines 和 areas 对于每条记录都是可变长度的.在这种情况下，您必须循环输入并提取学科和领域的最高计数值，然后动态构建属性数组:

However, I am assuming that disciplines and areas will be variable length for each record. In that case, you will have to loop over the input and pull the highest count value for both disciplines and areas, then build the properties array dynamically:

$inputData = Get-Content 'PATH_TO	est.json' -Raw | ConvertFrom-Json
$counts = $inputData | Select-Object -Property     @{Name='disciplineCount';Expression={$_.disciplines.Count}},@{Name='areaCount';Expression={$_.areas.count}}
$maxDisciplines = $counts | Measure-Object -Maximum -Property disciplineCount | Select-Object -ExpandProperty     Maximum
$maxAreas = $counts | Measure-Object -Maximum -Property areaCount | Select-Object -ExpandProperty Maximum

$properties=@('id','title','slug','url','email','link','subject','level')

1..$maxDisciplines | % {
  $properties += @{Name="discipline_$_";Expression=[scriptblock]::create("`$_.disciplines[$($_ - 1)]")}
}

1..$maxAreas | % {
  $properties += @{Name="area_$_";Expression=[scriptblock]::create("`$_.areas[$($_ - 1)]")}
}

$inputData | Select-Object -Property $properties | Export-CSV -NoTypeInformation -Path 'PATH_TO	est.csv'

此代码尚未经过全面测试，因此可能需要进行一些调整才能 100% 工作，但我相信这些想法是可靠的 =)

This code hasn't been fully tested, so it may need some tweaking to work 100%, but I believe the ideas are solid =)

这篇关于将嵌套的 JSON 数组转换为 CSV 文件中的单独列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！