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

gr8qqesn  于 2023-09-27  发布在  其他
关注(0)|答案(2)|浏览(105)

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

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

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

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

然而,对于学科和领域,我在生成的CSV文件中获得System.Object[]。
有没有办法把所有这些嵌套的值作为一个单独的列在CSV文件中,如area_1,area_2等。纪律也是如此。

rt4zxlrg

rt4zxlrg1#

2017-11-20,完全重写函数以提高性能,并添加-ArrayBase功能,支持PSStandardMembers和分组对象。

Flatten-Object

递归地解析包含数组、哈希表和(自定义)对象的对象。提供的objects will be aligned的所有添加属性以及其余对象。
需要PowerShell版本2或更高版本。

Cmdlet

Function Flatten-Object {                                       # Version 00.02.12, by iRon
    [CmdletBinding()]Param (
        [Parameter(ValueFromPipeLine = $True)][Object[]]$Objects,
        [String]$Separator = ".", [ValidateSet("", 0, 1)]$Base = 1, [Int]$Depth = 5, [Int]$Uncut = 1,
        [String[]]$ToString = ([String], [DateTime], [TimeSpan]), [String[]]$Path = @()
    )
    $PipeLine = $Input | ForEach {$_}; If ($PipeLine) {$Objects = $PipeLine}
    If (@(Get-PSCallStack)[1].Command -eq $MyInvocation.MyCommand.Name -or @(Get-PSCallStack)[1].Command -eq "<position>") {
        $Object = @($Objects)[0]; $Iterate = New-Object System.Collections.Specialized.OrderedDictionary
        If ($ToString | Where {$Object -is $_}) {$Object = $Object.ToString()}
        ElseIf ($Depth) {$Depth--
            If ($Object.GetEnumerator.OverloadDefinitions -match "[\W]IDictionaryEnumerator[\W]") {
                $Iterate = $Object
            } ElseIf ($Object.GetEnumerator.OverloadDefinitions -match "[\W]IEnumerator[\W]") {
                $Object.GetEnumerator() | ForEach -Begin {$i = $Base} {$Iterate.($i) = $_; $i += 1}
            } Else {
                $Names = If ($Uncut) {$Uncut--} Else {$Object.PSStandardMembers.DefaultDisplayPropertySet.ReferencedPropertyNames}
                If (!$Names) {$Names = $Object.PSObject.Properties | Where {$_.IsGettable} | Select -Expand Name}
                If ($Names) {$Names | ForEach {$Iterate.$_ = $Object.$_}}
            }
        }
        If (@($Iterate.Keys).Count) {
            $Iterate.Keys | ForEach {
                Flatten-Object @(,$Iterate.$_) $Separator $Base $Depth $Uncut $ToString ($Path + $_)
            }
        }  Else {$Property.(($Path | Where {$_}) -Join $Separator) = $Object}
    } ElseIf ($Objects -ne $Null) {
        @($Objects) | ForEach -Begin {$Output = @(); $Names = @()} {
            New-Variable -Force -Option AllScope -Name Property -Value (New-Object System.Collections.Specialized.OrderedDictionary)
            Flatten-Object @(,$_) $Separator $Base $Depth $Uncut $ToString $Path
            $Output += New-Object PSObject -Property $Property
            $Names += $Output[-1].PSObject.Properties | Select -Expand Name
        }
        $Output | Select ([String[]]($Names | Select -Unique))
    }
}; Set-Alias Flatten Flatten-Object

语法

<Object[]> Flatten-Object [-Separator <String>] [-Base "" | 0 | 1] [-Depth <Int>] [-Uncut<Int>] [ToString <Type[]>]

或:

Flatten-Object <Object[]> [[-Separator] <String>] [[-Base] "" | 0 | 1] [[-Depth] <Int>] [[-Uncut] <Int>] [[ToString] <Type[]>]

参数设置

-Object[] <Object[]>

要展平的对象(或多个对象)。

-Separator <String>(默认:.

递归属性名称之间使用的分隔符。.

-Depth <Int>(默认:5

递归属性的最大扁平化深度.任何负值都将导致无限的深度,并可能导致无限循环。

-Uncut <Int>(默认:1

object 迭代的次数将被限制为DefaultDisplayPropertySet。任何负值都将显示所有对象的所有属性。

-Base "" | 0 | 1(默认:1

嵌入数组的第一个索引名称:

  • 1,阵列将基于1:<Parent>.1<Parent>.2<Parent>.3,.
  • 0,数组将以0为基数:<Parent>.0<Parent>.1<Parent>.2,.
  • "",数组中的第一个元素将是未命名的,然后后跟1:<Parent><Parent>.1<Parent>.2,.
    -ToString <Type[]= [String], [DateTime], [TimeSpan]>

值类型列表(默认值为[String], [DateTime], [TimeSpan]),将被转换为字符串,而不是进一步扁平化。例如,[DateTime]可以被平坦化,具有附加属性,如DateDayDayOfWeek等。但是将被转换为单个(String)属性。

  • 注意事项:*

参数**-Path**供内部使用,但可以用作属性名称的前缀。

示例

回答具体问题:

(Get-Content "PATH_TO\test.json" -Raw | ConvertFrom-Json) | Flatten-Object | Convertto-CSV -NoTypeInformation | Set-Content "PATH_TO\test.csv"

测试结果:

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

对更复杂的自定义对象进行压力测试:

New-Object PSObject @{
    String    = [String]"Text"
    Char      = [Char]65
    Byte      = [Byte]66
    Int       = [Int]67
    Long      = [Long]68
    Null      = $Null
    Booleans  = $False, $True
    Decimal   = [Decimal]69
    Single    = [Single]70
    Double    = [Double]71
    Array     = @("One", "Two", @("Three", "Four"), "Five")
    HashTable = @{city="New York"; currency="Dollar"; postalCode=10021; Etc = @("Three", "Four", "Five")}
    Object    = New-Object PSObject -Property @{Name = "One";   Value = 1; Text = @("First", "1st")}
} | Flatten

测试结果:

Double               : 71
Decimal              : 69
Long                 : 68
Array.1              : One
Array.2              : Two
Array.3.1            : Three
Array.3.2            : Four
Array.4              : Five
Object.Name          : One
Object.Value         : 1
Object.Text.1        : First
Object.Text.2        : 1st
Int                  : 67
Byte                 : 66
HashTable.postalCode : 10021
HashTable.currency   : Dollar
HashTable.Etc.1      : Three
HashTable.Etc.2      : Four
HashTable.Etc.3      : Five
HashTable.city       : New York
Booleans.1           : False
Booleans.2           : True
String               : Text
Char                 : A
Single               : 70
Null                 :

展平编组对象:
$csv | Group Name | Flatten | Format-Table# https://stackoverflow.com/a/47409634/1701026
展平常用对象:
(Get-Process)[0] | Flatten-Object
或者是一个对象列表(数组):
Get-Service | Flatten-Object -Depth 3 | Export-CSV Service.csv
请注意,下面的命令需要几个小时来计算:
Get-Process | Flatten-Object | Export-CSV Process.csv

  • 为什么?* 因为它会导致一个有几百行和几千列的表。因此,如果你想使用它来进行扁平化处理,你最好限制行数(使用Where-Object小窗口)或列数(使用Select-Object小窗口)。
gk7wooem

gk7wooem2#

CSV转换/导出cmdlet无法“扁平化”对象,我可能遗漏了一些东西,但我不知道如何使用内置的cmdlet或功能来实现这一点。如果你可以保证disciplinesareas总是有相同数量的元素,你可以通过使用Select-Object和派生属性来实现这一点:

$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'

但是,我假设disciplinesareas对于每条记录都是可变长度的。在这种情况下,您将不得不循环输入并为规程和区域提取最高计数值,然后动态构建属性数组:

$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%工作,但我相信这些想法是可靠的=)

相关问题