将嵌套的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\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 =)

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