Compare-Object对比数组未返回预期匹配行的技术咨询

场景说明 #

我用PowerShell的Compare-Object对比两个数组：$dataFields是Git仓库里数据模型的所有字段列表，$reportFields是生产环境报表用的字段列表，目的是确认删除数据模型字段会不会影响报表。但执行命令后，只有$reportFields的第一行匹配，剩下的都显示差异，和预期不符。

示例数据 #

$dataFields示例：

Dataset   Table             Field
-------   -----             -----
Sales     BusinessUnit      BusinessUnit

$reportFields示例：

Workspace       Report              Dataset   Page                    Object       Type      Table             Field              
---------       ------              -------   ----                    ------       ----      -----             -----              
Sales           Invoices            Sales     Analysis                             Rows      BusinessUnit      BusinessUnit 
Sales           Customers           Sales     ReportLevel             ReportFilter Filter    BusinessUnit      BusinessUnit 
Sales           Customers           Sales     Customer Insight                     Category  BusinessUnit      BusinessUnit 
Sales           Customers           Sales     Customer Analysis                    ExplainBy BusinessUnit      BusinessUnit 
Sales           Revenue             Sales     ReportLevel             ReportFilter Filter    BusinessUnit      BusinessUnit 
Sales           Revenue             Sales     Analysis                             Rows      BusinessUnit      BusinessUnit 
NewServices     Projects            Sales     ReportLevel             ReportFilter Filter    BusinessUnit      BusinessUnit 

执行的命令 #

$compareObjects = @{
    ReferenceObject  = $reportFields 
    DifferenceObject = $dataFields 
}

Compare-Object @compareObjects -Property Dataset, Table, Field -IncludeEqual -PassThru | Format-Table

返回结果 #

Workspace       Report              Dataset   Page                    Object       Type      Table             Field        SideIndicator      
---------       ------              -------   ----                    ------       ----      -----             -----        -----           
Sales           Invoices            Sales     Analysis                             Rows      BusinessUnit      BusinessUnit == 
Sales           Customers           Sales     ReportLevel             ReportFilter Filter    BusinessUnit      BusinessUnit =>
Sales           Customers           Sales     Customer Insight                     Category  BusinessUnit      BusinessUnit =>
Sales           Customers           Sales     Customer Analysis                    ExplainBy BusinessUnit      BusinessUnit =>
Sales           Revenue             Sales     ReportLevel             ReportFilter Filter    BusinessUnit      BusinessUnit =>
Sales           Revenue             Sales     Analysis                             Rows      BusinessUnit      BusinessUnit =>
NewServices     Projects            Sales     ReportLevel             ReportFilter Filter    BusinessUnit      BusinessUnit =>

原因分析 #

Compare-Object默认是按顺序匹配对象，而且对于重复的匹配项，只会匹配第一组符合条件的记录。因为$dataFields里只有一条记录，Compare-Object把$reportFields的第一条和它匹配后，剩下的记录找不到对应的匹配项，就会被标记为差异。

解决方案 #

要让所有符合Dataset, Table, Field条件的记录都被识别为匹配，这里提供几种可行的方法：

方法1：直接筛选匹配项 #

先提取$dataFields的关键属性组合，再逐个检查$reportFields的记录是否匹配：

# 提取数据模型的关键字段组合（去重）
$dataKeySet = $dataFields | Select-Object Dataset, Table, Field -Unique

# 给报表字段标记是否在数据模型中存在
$reportFields | ForEach-Object {
    $currentField = $_
    # 检查当前记录是否在数据模型中存在
    $isMatched = $dataKeySet | Where-Object {
        $_.Dataset -eq $currentField.Dataset -and
        $_.Table -eq $currentField.Table -and
        $_.Field -eq $currentField.Field
    }
    # 添加匹配状态属性
    $_ | Add-Member -MemberType NoteProperty -Name 'ExistsInDataModel' -Value ($isMatched -ne $null) -PassThru
} | Format-Table Workspace, Report, Dataset, Table, Field, ExistsInDataModel

方法2：调整Compare-Object的使用方式 #

如果一定要用Compare-Object，可以先将两个数组转换为仅包含对比属性的对象，再通过分组来处理重复匹配：

# 生成仅包含对比属性的对象集合
$refItems = $reportFields | Select-Object Dataset, Table, Field, * -ExcludeProperty SideIndicator
$diffItems = $dataFields | Select-Object Dataset, Table, Field, * -ExcludeProperty SideIndicator

# 执行对比，然后关联回原报表的完整属性
Compare-Object -ReferenceObject $refItems -DifferenceObject $diffItems -Property Dataset, Table, Field -IncludeEqual -PassThru |
    ForEach-Object {
        # 找到原报表中对应的完整记录
        $originalRecord = $reportFields | Where-Object {
            $_.Dataset -eq $_.Dataset -and $_.Table -eq $_.Table -and $_.Field -eq $_.Field -and
            $_.Workspace -eq $_.Workspace -and $_.Report -eq $_.Report
        }
        # 添加对比标识
        $originalRecord | Add-Member -MemberType NoteProperty -Name 'SideIndicator' -Value $_.SideIndicator -PassThru
    } | Format-Table

方法3：分组统计匹配情况 #

通过合并两个数组并按关键属性分组，来判断每个报表字段是否在数据模型中存在：

# 合并两个数组并标记来源
$combined = $reportFields | Select-Object *, Source='Report' -PassThru
$combined += $dataFields | Select-Object *, Source='DataModel' -PassThru

# 按关键属性分组
$groupedFields = $combined | Group-Object Dataset, Table, Field

# 分析每组，给报表字段标记匹配状态
$groupedFields | ForEach-Object {
    $hasDataModelEntry = $_.Group.Source -contains 'DataModel'
    # 只处理报表来源的记录
    $_.Group | Where-Object Source -eq 'Report' | ForEach-Object {
        $_ | Add-Member -MemberType NoteProperty -Name 'ExistsInDataModel' -Value $hasDataModelEntry -PassThru
    }
} | Format-Table Workspace, Report, Dataset, Table, Field, ExistsInDataModel

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