Filter documents joined with $lookup by the inclusion of an array value

[Chris_Buchert]

I’ve got two collections: Organization and User. A typical organization document looks something like this:

type Organization = {
  "_id": string,
  // This looks a little moronic to have both an id and an orgId.
  // This is out of necessity, as pipeline variables cannot be drawn
  // from values that start with an _, as best as I understand it.
  // When I try to start a variable with a _, Compass indicates that
  // "<variable> starts with an invalid character for a user variable name"
  "orgId": string,
}

A typical user looks something like this:

type Permissions = "orgAdmin"

type User = {
  "_id": string,
  "orgId": string,
  "permissions": Permissions[],
}

I’m trying to build an aggregation that will do a left join between Organizations and Users, including all organization users that have orgAdmin permissions. Here it is at the moment:

{
  from: 'User',
  let: {
    "orgId": "$orgId",
    "permissions": "$permissions"
  },
  as: 'orgAdmins',
  pipeline: [
    {
      $match: {
        $and: [
          {
            $expr: {
              $eq: ["$orgId", "$$orgId"]
            }
          },
          {
            $expr: {
              $in: ["orgAdmin", "$$permissions"]
            }
          }
        ]
      }
    },
  ]
}

Joining on orgId is a no brainer. It works great. The trouble I’m running into is around the array value inclusion check. My understanding is that access to fields in joined documents requires defining variables using let, and that accessing the variables in your pipeline is done with $$ (not the $ used in the variable’s declaration). However, when I try to evaluate the aggregation in Compass I get the following warning and no returned records:

Failed to optimize pipeline :: caused by :: Failed to optimize expression :: caused by :: Failed to optimize expression :: caused by :: $in requires an array as a second argument, found: missing

I’m totally stumped. I’m not sure how to pass the permissions array to the $in aggregation. Any help would be appreciated.

Thanks in advance,

Chris.

[Prasad_Saya]

Hello @Chris_Buchert,

You need to use the Aggregate Operator $in. This allows you pass the permissions array as well as the string "orgAdmin" to the operator.

Note that MongoDB aggregate operators are different from MQL (MongoDB Query Language) operators. So, there are two operators with similar “look”, $in.

Whenever you use any operators within the $expr, you must use Aggregate Expression Operators (and cannot use the MQL operators). Hence the error “… $in requires an array as a second argument, found: missing”

[Chris_Buchert]

Thanks for getting back to me, Prasad.

I’ve been using the $in aggregation doc as my primary source of documentation. Will you show me how the $in aggregation operator should be used differently in my implementation?

[Prasad_Saya]

@Chris_Buchert, you are right about the syntax of the $in you had used - it is correct. I suggest you use the $expr for the entire filter, like this and see how it works:

    $match: {
      $expr: { 
        $and: [ 
          { $eq: [ "$orgId", "$$orgId" ] }, 
          { $in: [ "orgAdmin", "$$permissions" ] } 
        ] 
      }
    }

[Chris_Buchert]

The update to my code is yeiding the same results as my initial query. It seems like the $permissions variable isn’t holding the value I’m expecting. I double-checked the structure of my User model and it’s represented accurately in the TypeScript code above. Here is a screenshot of an example user (there are some additional fields that I didn’t include in my description as they shouldn’t be relevant to the query (the query is being executed in Compass)).

My updated query looks like this:

{
  from: 'User',
  let: {
    "orgId": "$orgId",
    "permissions": "$permissions"
  },
  as: 'orgAdmins',
  pipeline: [
    {
      $match: {
        $expr: {
          $and: [
            { $eq: ["$orgId", "$$orgId"] },
            { $in: ["orgAdmin", "$$permissions"] }
          ]
        }
      }
    }
  ]
}

It’s a lot cleaner, but is still failing with a warning:

Failed to optimize pipeline :: caused by :: Failed to optimize expression :: caused by :: $in requires an array as a second argument, found: missing

Commenting out the $in aggregation returns data without drama:

Is there a way to print variable values for debugging?

[Prasad_Saya]

@Chris_Buchert

{
  from: 'User',
  let: {
    "orgId": "$orgId",
    "permissions": "$permissions"
  },
  as: 'orgAdmins',
  pipeline: [
    {
      $match: {
        $expr: {
          $and: [
            { $eq: ["$orgId", "$orgId"] },
            { $in: ["orgAdmin", "$permissions"] }
          ]
        }
      }
    }
  ]
}

Hello @Chris_Buchert, here is the corrected code (and this will work without errors):

{
  from: "User",
  let: { orgIdVar: "$orgId" },
  as: "orgAdmins",
  pipeline: [
    {
      $match: {
        $expr: {
             $and: [
                 { $eq: [ "$orgId", "$$orgIdVar" ] },
                 { $in: [ "orgAdmin", "$permissions" ] }
             ]
        }
      }
    },
  ]
}