如何用PowerShell提取大JSONL文件中的深层嵌套键？

问题背景 #

有一个超过1GB的JSONL格式词典文件，单条数据示例如下：

{"pos": "name", "head_templates": [{"name": "head", "args": {"1": "ang", "2": "proper nouns", "head": "", "sort": "", "g": "m", "g2": "", "g3": "", "g4": "", "g5": ""}, "expansion": "December m"}, {"name": "ang-proper noun", "args": {"g": "m"}, "expansion": "December m"}], "word": "December", "lang": "Old English", "lang_code": "ang", "sounds": [{"ipa": "/ˈde.kem.ber/"}], "senses": [{"links": [["December", "December#English"]], "glosses": ["December"], "tags": ["masculine"], "id": "en-December-ang-name-j7IEykYJ", "categories": [{"name": "Old English entries with incorrect language header", "kind": "other", "parents": ["Entries with incorrect language header", "Entry maintenance"], "source": "w"}, {"name": "Pages with 4 entries", "kind": "other", "parents": [], "source": "w"}, {"name": "Pages with entries", "kind": "other", "parents": [], "source": "w"}, {"name": "Months", "kind": "topical", "parents": ["Periodic occurrences", "Time", "All topics", "Fundamental"], "source": "w", "orig": "ang:Months", "langcode": "ang"}], "synonyms": [{"word": "ǣrra ġēola"}]}]}

需要提取指定的顶层及深层嵌套键生成新文件，目标键列表如下：

$keysToInclude = @(
    "coordinate_terms", "coordinate_terms.english", "coordinate_terms.roman", "coordinate_terms.ruby",
    "coordinate_terms.sense", "coordinate_terms.tags", "coordinate_terms.word", "forms", "forms.form",
    "forms.roman", "forms.ruby", "forms.source", "forms.tags", "lang", "pos", "related", "related.english",
    "related.roman", "related.ruby", "related.sense", "related.tags", "related.word", "senses", "senses.alt_of",
    "senses.alt_of.extra", "senses.alt_of.word", "senses.coordinate_terms", "senses.coordinate_terms.alt",
    "senses.coordinate_terms.english", "senses.coordinate_terms.roman", "senses.coordinate_terms.sense",
    "senses.coordinate_terms.tags", "senses.coordinate_terms.word", "senses.form_of", "senses.form_of.extra",
    "senses.form_of.word", "senses.glosses", "senses.raw_tags", "senses.related", "senses.related.alt",
    "senses.related.english", "senses.related.roman", "senses.related.ruby", "senses.related.sense",
    "senses.related.tags", "senses.related.word", "senses.synonyms", "senses.synonyms.alt", "senses.synonyms.english",
    "senses.synonyms.extra", "senses.synonyms.raw_tags", "senses.synonyms.roman", "senses.synonyms.ruby",
    "senses.synonyms.sense", "senses.synonyms.tags", "senses.synonyms.word", "senses.tags", "senses.translations",
    "senses.translations.alt", "senses.translations.english", "senses.translations.lang", "senses.translations.note",
    "senses.translations.raw_tags", "senses.translations.roman", "senses.translations.sense", "senses.translations.tags",
    "senses.translations.word", "sounds", "sounds.enpr", "sounds.hangeul", "sounds.homophone", "sounds.ipa",
    "sounds.tags", "sounds.zh-pron", "translations", "translations.alt", "translations.english", "translations.lang",
    "translations.raw_tags", "translations.roman", "translations.sense", "translations.tags", "translations.word", "word"
)

但现有代码只能提取顶层键，无法处理topkey.nestedkey格式的深层路径：

$currentLine = 0

foreach ($line in $jsonLines) {
    $currentLine++
    $jsonObject = $line | ConvertFrom-Json

    # Extract the values for the specified keys
    foreach ($key in $keysToInclude) {
        $value = $jsonObject.PSObject.Properties[$key].Value
        if ($value -is [Array]) {
            $value = $value -join '|'
        }
        $csvRow[$key] = $value
    }
}

解决方案 #

核心是实现一个能解析点分隔键路径的函数，逐层访问嵌套对象的属性，同时针对大文件优化内存占用。

完整实现代码 #

# 定义解析嵌套键路径的函数
function Get-NestedValue {
    param(
        [Parameter(Mandatory)]
        $Object,
        [Parameter(Mandatory)]
        [string]$KeyPath
    )

    $current = $Object
    # 拆分键路径为层级数组
    $keys = $KeyPath.Split('.')
    
    foreach ($key in $keys) {
        # 处理数组类型：遍历所有元素提取对应键值，递归处理深层嵌套
        if ($current -is [array]) {
            $values = @()
            foreach ($item in $current) {
                if ($item.PSObject.Properties[$key]) {
                    $val = $item.PSObject.Properties[$key].Value
                    # 若剩余键路径未走完，继续递归解析
                    $remainingKeys = $keys[$keys.IndexOf($key)+1..($keys.Count-1)] -join '.'
                    if ($remainingKeys -and ($val -is [PSCustomObject] -or $val -is [array])) {
                        $val = Get-NestedValue -Object $val -KeyPath $remainingKeys
                    }
                    $values += $val
                }
            }
            # 数组元素用|拼接，和原代码逻辑一致
            return $values -join '|'
        }
        # 处理对象类型：直接访问属性
        elseif ($current.PSObject.Properties[$key]) {
            $current = $current.PSObject.Properties[$key].Value
        }
        else {
            # 路径不存在时返回空值
            return $null
        }
    }
    # 处理最终值，数组类型同样拼接
    if ($current -is [array]) {
        return $current -join '|'
    }
    return $current
}

# 主处理逻辑
$inputFile = "kaikki.org-dictionary-all-by-pos-name.jsonl"
$outputFile = "extracted_data.csv"

# 写入CSV表头
$keysToInclude -join ',' | Out-File -FilePath $outputFile -Encoding utf8

# 批量读取行，避免加载整个大文件到内存
Get-Content -Path $inputFile -ReadCount 1000 | ForEach-Object {
    foreach ($line in $_) {
        if ([string]::IsNullOrWhiteSpace($line)) { continue }
        try {
            $jsonObject = $line | ConvertFrom-Json -ErrorAction Stop
            $csvRow = [ordered]@{}
            
            foreach ($key in $keysToInclude) {
                $csvRow[$key] = Get-NestedValue -Object $jsonObject -KeyPath $key
            }
            
            # 将行转为CSV格式追加到文件
            $csvRow.GetEnumerator() | ForEach-Object { $_.Value } | Join-String -Separator ',' | Out-File -FilePath $outputFile -Encoding utf8 -Append
        }
        catch {
            Write-Warning "处理行时出错: $_"
        }
    }
}

关键说明 #

1. 嵌套键解析：Get-NestedValue函数拆分点分隔的键路径，逐层遍历对象或数组，递归处理深层嵌套结构，完美适配topkey.nestedkey格式的路径。
2. 数组处理：遇到数组时自动遍历所有元素提取对应值，并用|拼接，和原代码逻辑保持一致。
3. 大文件优化：使用Get-Content -ReadCount 1000批量读取行，避免一次性加载1GB文件到内存，大幅降低内存占用。
4. 容错处理：加入异常捕获，单个错误行不会导致整个脚本中断，同时输出警告信息便于排查问题。

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