将嵌套的JSON数组转换为CSV文件中的单独列

本文介绍了将嵌套的JSON数组转换为CSV文件中的单独列的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个看起来像这样的JSON文件:

I have a JSON file that looks like this:

{ "id": 10011, "title": "Test procedure", "slug": "slug", "url": "test.test", "email": "test@test", "link": "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\test.json" -Raw | ConvertFrom-Json)| Convertto-CSV -NoTypeInformation | Set-Content "PATH_TO\test.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\test.json' -Raw | ConvertFrom-Json)| Select-Object -Property $properties | Export-CSV -NoTypeInformation -Path 'PATH_TO\test.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\test.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\test.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 =)