powershell - filter nested object which contains data from list

user_14649045 0
Viewed 219

I am looking for a way to filter nested objects with PowerShell. Let's say I have a list that contains UPN addresses.

$UPN = "user1@xyz.com", "user5@xyz.com"

In the below object converted to JSON, I want to get only those objects where "Attr1" within whole object contains any address from the above list.

[
    {
        "Id":  "1",
        "Name":  "XYZ",
        "Attr1":  [
                      {
                          "Id":  "1",
                          "UPN":  "user1@xyz.com"
                      },
                      {
                          "Id":  "2",
                          "UPN":  "user2@xyz.com"
                      },
                      {
                          "Id":  "3",
                          "UPN":  "user3@xyz.com"
                      }
                  ],
        "Attr2":  [
                        {
                            "Id":  "1",
                            [...]
                        }
                    ],
        "Attr3":  [
                         {
                             "Id":  "1",
                             [...]
                         },
                         {
                             "Id":  "2",
                             [...]
                        
                         }
                     ]
    },
    {
        "Id":  "2",
        "Name":  "XYZ",
        "Attr1":  [
                      {
                          "Id":  "1",
                          "UPN":  "user2@xyz.com"
                      }
                  ],
        "Attr2":  [
        
                    ],
        "Attr3":  [
                         {
                             "Id":  "1",
                             [...]
                         }
                     ]
    }
]

How to filter such an object? The standard method to not work for me (I am not getting the desired result, losing data):

$CollectionOfObjects | Where-Object {$_.Attr1.UPN -in $UPN}

How to handle this?

Regards!

powershell
1 Answers

You need to nest Where-Object calls to get the desired result, or, for better performance with in-memory collections, .Where() method calls:

$CollectionOfObjects.Where({ $_.Attr1.Where({ $_.UPN -in $UPN }, 'First') })

Tip of the hat to Manuel Batsching for his help.

Note: The 'First' argument isn't strictly necessary, but can improve performance, as it stops enumerating the elements of the array in .Attr1 as soon as a match is found.

As an aside: Where-Object has no equivalent feature - enumeration invariably continues.
GitHub issue #13834 suggests bringing all the extra features .Where() supports to Where-Object too.

A self-contained example with simplified data:

$UPN = 'user1@xyz.com', 'user6@xyz.com'

$CollectionOfObjects = ConvertFrom-Json @'
[
  {
      "Id":  "1",
      "Name":  "XYZ1",
      "Attr1":  [
                    {
                        "Id":  "1",
                        "UPN":  "user1@xyz.com"
                    },
                    {
                        "Id":  "2",
                        "UPN":  "user2@xyz.com"
                    },
                    {
                        "Id":  "3",
                        "UPN":  "user3@xyz.com"
                    }
                ],
      "Attr2":  [ ],
      "Attr3":  [ ]
  },
  {
    "Id":  "2",
    "Name":  "XYZ2",
    "Attr1":  [
                  {
                      "Id":  "1",
                      "UPN":  "user4@xyz.com"
                  },
                  {
                      "Id":  "2",
                      "UPN":  "user5@xyz.com"
                  }
              ],
    "Attr2":  [ ],
    "Attr3":  [ ]
  },
  {
      "Id":  "3",
      "Name":  "XYZ3",
      "Attr1":  [
                    {
                        "Id":  "1",
                        "UPN":  "user6@xyz.com"
                    }
                ],
      "Attr2":  [ ],
      "Attr3":  [ ]
  }
]
'@

$CollectionOfObjects.Where({ $_.Attr1.Where({ $_.UPN -in $UPN }, 'First') })

Output, showing that the two objects of interest were successfully identified:

Id    : 1
Name  : XYZ1
Attr1 : {@{Id=1; UPN=user1@xyz.com}, @{Id=2; UPN=user2@xyz.com}, @{Id=3; UPN=user3@xyz.com}}
Attr2 : {}
Attr3 : {}

Id    : 3
Name  : XYZ3
Attr1 : {@{Id=1; UPN=user6@xyz.com}}
Attr2 : {}
Attr3 : {}
user_453750
Related