Powershell - issue with matching an object within a list with another object within another list of object

Question

yep, i dunno if i can make a longer title ... ;)

well, here is what i got :
some lists of objects (lets call them "objA[0..z]") in "listA[1..z]"
some lists of objects (lets call them "objB[0..z]") in "listB[1..z]"

Here is a Dataset (modified from answer below)

$lista1 = @'
[
    {
        "computerName": "123",
        "userName": "Antoine",
        "otherData1": "test1.xls",
        "otherData2": "yyy"
    },
    {
        "computerName": "123",
        "userName": "Antoine",
        "otherData1": "test2.xls",
        "otherData2": "yyy"
    },
    {
        "computerName": "456",
        "userName": "Benoit",
        "otherData1": "file1.txt",
        "otherData2": "yyy"
    }
]
'@

$lista2 = @'
[
    {
        "computerName": "789",
        "userName": "Cyril",
        "otherData1": "file1.pps",
        "otherData2": "yyy"
    },
    {
        "computerName": "789",
        "userName": "Cyril",
        "otherData1": "file2.pps",
        "otherData2": "yyy"
    },
    {
        "computerName": "321",
        "userName": "Damien",
        "otherData1": "doc.docx",
        "otherData2": "yyy"
    }
]
'@

$listb1 = @'
[
    {
        "computerName": "789",
        "userName": "Cyril",
        "otherData1": "file2.pps",
        "otherData2": "yyy"
    },
    {
        "computerName": "321",
        "userName": "Damien",
        "otherData1": "doc.docx",
        "otherData2": "bbb"
    }
]
'@

$listb2 = @'
[
    {
        "computerName": "123",
        "userName": "Antoine",
        "otherData1": "test1.xls",
        "otherData2": "yyy"
    },
    {
        "computerName": "123",
        "userName": "Antoine",
        "otherData1": "test2.xls",
        "otherData2": "yyy"
    },
]
'@


# expected output with this dataset : 
# 1st and 2nd are not in any listb and 3rd.otherData2 do not match with each other

[
{
    "computerName": "456",
    "userName": "Benoit",
    "otherData1": "file1.txt",
    "otherData2": "yyy"
},
{
    "computerName": "789",
    "userName": "Cyril",
    "otherData1": "file1.pps",
    "otherData2": "yyy"
},
{
    "computerName": "321",
    "userName": "Damien",
    "otherData1": "doc.docx",
    "otherData2": "yyy"
}
]

what i'm trying to do :

If (obj'w' in lista'x') match computerName and userName and otherData1 in (obj'y' in listb'z')
AND IF same object NOT match otherData2 -> return said object

ELSE IF (obj'w' in lista'x') NOT match any object in every listb[0..z] -> return said object

here is what i did so far and where i'm stuck.
I more or less managed to make it work with "single file" on both listA & listB

Problem1 is i'm clearly drowing in foreach loop and i fail to find a "clean way".
Problem2 is i fail to properly check if objA is present in listB resulting in not being able to return it. (or returning all objects)

$listAPath = 'D:\A'
$listBPath = 'D:\B'

# multiples files in each directory
$listAFiles = Get-ChildItem $listAPath
$listBFiles = Get-ChildItem $listBPath

$failList = foreach ($listAFile in $listAFiles) {
    $listObjA = Get-Content $listAFile.fullname -raw | ConvertFrom-Json
    foreach ($objA in $listObjA) {
        foreach ($listBFile in $listBFiles) {
            $listObjB = Get-Content $listBFile.fullname -raw | ConvertFrom-Json
            foreach ($objB in $listObjB) {
                if ($objA.computerName -eq $objB.computerName -and $objA.userName -eq $objB.userName -and $objA.otherData1 -eq $objB.otherData1) {
                    if (<# do something with OtherData2 #>) {
                        $ObjA
                    }
                } <# else {return $objA} #>
            }
        }
    }
}

$failList

Reworked this script while typing it, hope i did not forget anything while doing it.

I'd be glad if i could get help here
If you need some more information, do not hesitate to ask.

Ty in advance.

EDITED : trying to apply what was asked in comment / answer, did not modify first "version" of script provided even though it might no match anymore with given dataset and reworked informations... will edit if asked.
For thoose who are curious, otherData2 is a datetime data with the "real" script.

So you have two directories of json files and you want to compare them to each other based on three properties? — Maximilian Burszley, Oct 07 '20 at 16:52
it is not the json file that i want to compare but the objects within said files (file will differ, it is expected in my case) — Forever.Learner, Oct 07 '20 at 18:02

Doug Maurer · Accepted Answer · 2020-10-08T07:08:10.800

If I understand you correctly, if an object in A matches the 3 properties of an object in list b then you will check OtherData2 and return the object depending on that check. If it's not in list b then just return the object. I've created some sample data for this demonstration.

$lista = @'
[
    {
        "computerName": 123,
        "userName": "john",
        "otherData1": "data128",
        "otherData2": "yyy"
    },
    {
        "computerName": 456,
        "userName": "joan",
        "otherData1": "data821",
        "otherData2": "nnn"
    },
    {
        "computerName": 789,
        "userName": "doug",
        "otherData1": "data111",
        "otherData2": "abc"
    }
]
'@

$listb = @'
[
    {
        "computerName": 123,
        "userName": "john",
        "otherData1": "data128",
        "otherData2": "yyy"
    },
    {
        "computerName": 789,
        "userName": "doug",
        "otherData1": "data111",
        "otherData2": "abc"
    }
]
'@

$listObjA = $lista | ConvertFrom-Json
$listObjB = $listb | ConvertFrom-Json

foreach($objA in $listObjA)
{
    if($listObjB | where {$_.computername -eq $objA.computername -and
                          $_.username -eq $objA.username -and
                          $_.otherData1 -eq $objA.otherdata1})
    {
        if($objA.otherdata2 -eq 'yyy')
        {
            $objA
        }
    }
    else
    {
        $objA
    }
}

The output will be 2 objects from list a, one that was in list b AND otherdata2 met the criteria and the other was not in list b.

computerName userName otherData1 otherData2
------------ -------- ---------- ----------
         123 john     data128    yyy       
         456 joan     data821    nnn

Edit

Ok, first issue you're going to face is combining of the different JSON files that contain arrays. See the following test.

$listb1 = New-TemporaryFile
$listb2 = New-TemporaryFile

@'
[
    {
        "computerName": "789",
        "userName": "Cyril",
        "otherData1": "file2.pps",
        "otherData2": "yyy"
    },
    {
        "computerName": "321",
        "userName": "Damien",
        "otherData1": "doc.docx",
        "otherData2": "bbb"
    }
]
'@ | Set-Content $listb1 -Encoding UTF8

@'
[
    {
        "computerName": "123",
        "userName": "Antoine",
        "otherData1": "test1.xls",
        "otherData2": "yyy"
    },
    {
        "computerName": "123",
        "userName": "Antoine",
        "otherData1": "test2.xls",
        "otherData2": "yyy"
    }
]
'@ | Set-Content $listb2 -Encoding UTF8

$listObjB = $listb1,$listb2 | foreach {Get-content $_ -Raw | ConvertFrom-Json}

$listObjB.Count

2

You end up with 2 arrays of 2 objects, surely not what you were expecting. However, by simply putting a subexpression around the get-content | convert command, powershell will unroll those into individual items. See this excellent answer by the other commenter Maximillian Burszley for more info.

$listObjB = $listb1,$listb2 | foreach {(Get-content $_ -Raw | ConvertFrom-Json)}

$listObjB.Count

4

That's what we were expecting. We will need to apply the same to the obja list

$lista1 = New-TemporaryFile
$lista2 = New-TemporaryFile

@'
[
    {
        "computerName": "123",
        "userName": "Antoine",
        "otherData1": "test1.xls",
        "otherData2": "yyy"
    },
    {
        "computerName": "123",
        "userName": "Antoine",
        "otherData1": "test2.xls",
        "otherData2": "yyy"
    },
    {
        "computerName": "456",
        "userName": "Benoit",
        "otherData1": "file1.txt",
        "otherData2": "yyy"
    }
]
'@ | Set-Content $lista1 -Encoding UTF8

@'
[
    {
        "computerName": "789",
        "userName": "Cyril",
        "otherData1": "file1.pps",
        "otherData2": "yyy"
    },
    {
        "computerName": "789",
        "userName": "Cyril",
        "otherData1": "file2.pps",
        "otherData2": "yyy"
    },
    {
        "computerName": "321",
        "userName": "Damien",
        "otherData1": "doc.docx",
        "otherData2": "yyy"
    }
]
'@ | Set-Content $lista2 -Encoding UTF8

$listobjA = $lista1,$lista2 | foreach {(Get-content $_ -Raw | ConvertFrom-Json)}

Then make a slight change to my suggested solution

foreach($objA in $listobjA)
{
    if($matchedobj = $listObjB | where {$_.computername -eq $objA.computername -and
                          $_.username -eq $objA.username -and
                          $_.otherData1 -eq $objA.otherdata1})
    {
        if($objA.otherdata2 -ne $matchedobj.otherData2)
        {
            $objA
        }
    }
    else
    {
        $objA
    }
}

And we will end up with the expected results.

computerName userName otherData1 otherData2
------------ -------- ---------- ----------
456          Benoit   file1.txt  yyy       
789          Cyril    file1.pps  yyy       
321          Damien   doc.docx   yyy

You could also do the a conversion as part of the loop like this.

foreach($objA in $lista1,$lista2 | foreach {(Get-content $_ -Raw | ConvertFrom-Json)})
{
    if($matchedobj = $listObjB | where {$_.computername -eq $objA.computername -and
                            $_.username -eq $objA.username -and
                            $_.otherData1 -eq $objA.otherdata1})
    {
        if($objA.otherdata2 -ne $matchedobj.otherData2)
        {
            $objA
        }
    }
    else
    {
        $objA
    }
}

But I personally feel the former was easier to read and understand.

One thing this misses is outputting the list of paths where there is not a match. — Maximilian Burszley, Oct 07 '20 at 16:57
hi, and TY for you answer :) it does look like it "do the job", i have not the data set to try it on right now but will definitively try this tomorrow. I must admit i'm suprised with the way you do the check if objA is in listObjB, did not think that was possible this way. However correct me if i'm wrong but i still have to add 2 more "foreach" to iterate throuth my "files" right ? — Forever.Learner, Oct 07 '20 at 19:55
Is there a matching B file for every A file? Or is there a master B file that you need to check each A file against? — Doug Maurer, Oct 07 '20 at 20:09
actually it is kind of "messy" (my opinion) as there is a list of "A file" that i need to check against a list of "B file". each file List are in their own folder.I'm wondering if it would not be simpler to aggregate the file before checking them against each other. (aggregate fileListA then aggregate fileListB then check thoose aggregat against each other) not sure if i'm clear here. Also to answer you : each file is "solo", there is no teaming fileA1 vs fileB1 or anything like that — Forever.Learner, Oct 07 '20 at 20:41
So at a minimum you’d need to check each single a file against every b file? — Doug Maurer, Oct 07 '20 at 21:14
exact, (i tried to make a data set + modified script absed on @DougMaurer answer, what is the best practice to post it ? answer my own topic ?) spoiler : result aren't good xD — Forever.Learner, Oct 07 '20 at 21:37
Well if it doesn’t do the job I wouldn’t post it. Why don’t you edit your post with more details of how the file structure/naming is and how exactly you need to compare. Maybe a hint of what otherdata2 needs to be/match would be helpful as well. — Doug Maurer, Oct 07 '20 at 22:00
Your example shows an A folder and a B folder. Are all the files under each of those only these files? Or are there other files in there that aren’t needed? — Doug Maurer, Oct 07 '20 at 22:02
Also you should post more realistic examples. Your example json is not valid and only contains one entry. What do the real files *actually* look like? — Doug Maurer, Oct 07 '20 at 22:03
@DougMaurer edited the initial post (some information + dataset with expected result), hope my goal gonna be more understandable. and yes all files in folder named "A" are list of object on the "A side" same goes for files in folder "B" which are lists of object of the "B side". — Forever.Learner, Oct 07 '20 at 23:07
currently reading your answer and digesting it, (and link provided) i'll update you later in the day but thank you ! in the bits i have read, there already are "constructions" that i would not have thought of. On another note i do think there is a little typo when you create the temporary file and merge the array of object, you explain under it that we are supposed to get 2 arrays of 3 object. you probably inversed listA and listB ;) (another typo with missing caps on "$listobjb.Count") — Forever.Learner, Oct 08 '20 at 06:30
i'm playing with it actually and it work indeed. But i must admit that i fail to perfectly understand the parenthesis role here even after reading the link you provided. thanks for your time and work here ! — Forever.Learner, Oct 08 '20 at 16:59

Powershell - issue with matching an object within a list with another object within another list of object

1 Answers1