Using $in on multiple fields in MongoDB

Question

I am stuck on an issue related to $in in MongoDB, not getting the desired results.

Here is my MongoDB collection data for better visualisation

    [
        {
        "_id" : ObjectId("5aeaf7c73c3e9de82d91e439"),
        "companyID" : "4",
        "accounts" : [ 
            {
                "_id" : ObjectId("5aeaf7c720262a1db759edf5"),
                "userID" : "1",
                "preferences" : [ 
                    {
                        "emailNotification" : true,
                        "smsNotification" : true,
                        "pushNotification" : false,
                        "webNotification" : false,
                        "lastUpdatedBy" : "SYSTEM",
                        "_id" : ObjectId("5aeaf7c720262a1db759edf7"),
                        "preferenceID" : "6fbd6c-4c56-11e8-842f-0ed5f89f718b",
                        "createdAt" : ISODate("2018-05-03T11:51:35.509Z"),
                        "updatedAt" : ISODate("2018-05-03T11:51:35.509Z")
                    }, 
                    {
                        "emailNotification" : true,
                        "smsNotification" : true,
                        "pushNotification" : false,
                        "webNotification" : false,
                        "lastUpdatedBy" : "SYSTEM",
                        "_id" : ObjectId("5aeaf7c720262a1db759edf6"),
                        "preferenceID" : "6fb118-4c56-11e8-842f-0ed5f89f718b",
                        "createdAt" : ISODate("2018-05-03T11:51:35.509Z"),
                        "updatedAt" : ISODate("2018-05-03T11:51:35.509Z")
                    }
                ]
            }
        ],
        "__v" : 0
    },

    {
        "_id" : ObjectId("5aeafe693c3e9de82d91e43a"),
        "companyID" : "5",
        "accounts" : [ 
            {
                "_id" : ObjectId("5aeafe698b1d5f2057419c99"),
                "userID" : "1",
                "preferences" : [ 
                    {
                        "emailNotification" : true,
                        "smsNotification" : true,
                        "pushNotification" : false,
                        "webNotification" : false,
                        "lastUpdatedBy" : "SYSTEM",
                        "_id" : ObjectId("5aeafe698b1d5f2057419c9b"),
                        "preferenceID" : "6fbd6c-4c56-11e8-842f-0ed5f89f718b",
                        "createdAt" : ISODate("2018-05-03T12:19:53.436Z"),
                        "updatedAt" : ISODate("2018-05-03T12:19:53.436Z")
                    }, 
                    {
                        "emailNotification" : true,
                        "smsNotification" : true,
                        "pushNotification" : false,
                        "webNotification" : false,
                        "lastUpdatedBy" : "SYSTEM",
                        "_id" : ObjectId("5aeafe698b1d5f2057419c9a"),
                        "preferenceID" : "6fb118-4c56-11e8-842f-0ed5f89f718b",
                        "createdAt" : ISODate("2018-05-03T12:19:53.436Z"),
                        "updatedAt" : ISODate("2018-05-03T12:19:53.436Z")
                    }
                ]
            }
        ],
        "__v" : 0
    },
    {
        "_id" : ObjectId("5aeafe6d3c3e9de82d91e43b"),
        "companyID" : "6",
        "accounts" : [ 
            {
                "_id" : ObjectId("5aeafe6d8b1d5f2057419c9c"),
                "userID" : "1",
                "preferences" : [ 
                    {
                        "emailNotification" : true,
                        "smsNotification" : true,
                        "pushNotification" : false,
                        "webNotification" : false,
                        "lastUpdatedBy" : "SYSTEM",
                        "_id" : ObjectId("5aeafe6d8b1d5f2057419c9e"),
                        "preferenceID" : "6fbd6c-4c56-11e8-842f-0ed5f89f718b",
                        "createdAt" : ISODate("2018-05-03T12:19:57.331Z"),
                        "updatedAt" : ISODate("2018-05-03T12:19:57.331Z")
                    }, 
                    {
                        "emailNotification" : true,
                        "smsNotification" : true,
                        "pushNotification" : false,
                        "webNotification" : false,
                        "lastUpdatedBy" : "SYSTEM",
                        "_id" : ObjectId("5aeafe6d8b1d5f2057419c9d"),
                        "preferenceID" : "6fb118-4c56-11e8-842f-0ed5f89f718b",
                        "createdAt" : ISODate("2018-05-03T12:19:57.331Z"),
                        "updatedAt" : ISODate("2018-05-03T12:19:57.331Z")
                    }
                ]
            }, 
            {
                "_id" : ObjectId("5aeafe738b1d5f2057419c9f"),
                "userID" : "2",
                "preferences" : [ 
                    {
                        "emailNotification" : true,
                        "smsNotification" : true,
                        "pushNotification" : false,
                        "webNotification" : false,
                        "lastUpdatedBy" : "SYSTEM",
                        "_id" : ObjectId("5aeafe738b1d5f2057419ca1"),
                        "preferenceID" : "6fbd6c-4c56-11e8-842f-0ed5f89f718b",
                        "createdAt" : ISODate("2018-05-03T12:20:03.987Z"),
                        "updatedAt" : ISODate("2018-05-03T12:20:03.987Z")
                    }, 
                    {
                        "emailNotification" : true,
                        "smsNotification" : true,
                        "pushNotification" : false,
                        "webNotification" : false,
                        "lastUpdatedBy" : "SYSTEM",
                        "_id" : ObjectId("5aeafe738b1d5f2057419ca0"),
                        "preferenceID" : "6fb118-4c56-11e8-842f-0ed5f89f718b",
                        "createdAt" : ISODate("2018-05-03T12:20:03.987Z"),
                        "updatedAt" : ISODate("2018-05-03T12:20:03.987Z")
                    }
                ]
            }, 
            {
                "_id" : ObjectId("5aeafe778b1d5f2057419ca2"),
                "userID" : "3",
                "preferences" : [ 
                    {
                        "emailNotification" : true,
                        "smsNotification" : true,
                        "pushNotification" : false,
                        "webNotification" : false,
                        "lastUpdatedBy" : "SYSTEM",
                        "_id" : ObjectId("5aeafe778b1d5f2057419ca4"),
                        "preferenceID" : "6fbd6c-4c56-11e8-842f-0ed5f89f718b",
                        "createdAt" : ISODate("2018-05-03T12:20:07.062Z"),
                        "updatedAt" : ISODate("2018-05-03T12:20:07.062Z")
                    }, 
                    {
                        "emailNotification" : true,
                        "smsNotification" : true,
                        "pushNotification" : false,
                        "webNotification" : false,
                        "lastUpdatedBy" : "SYSTEM",
                        "_id" : ObjectId("5aeafe778b1d5f2057419ca3"),
                        "preferenceID" : "6fb118-4c56-11e8-842f-0ed5f89f718b",
                        "createdAt" : ISODate("2018-05-03T12:20:07.062Z"),
                        "updatedAt" : ISODate("2018-05-03T12:20:07.062Z")
                    }
                ]
            }
        ],
        "__v" : 0
    }]

What I am trying to get from the data. I am trying fetch each company's user preferences based on a companyID & userID, but for multiple company and multiple users, not for a single one.

Say for an example I have this input Data [userID:2,companyID: 6] and [userID:1, companyID:4]

The output based on above added documents data, should be

    [
        {
            "userID":2,
            "companyID":6,
            "preferences":[] // the preferences from that db
        },
        {
            "userID":1,
            "companyID":4,
            "preferences":[] // their respective preferences array
        }
    ]

The above is the desired output, Now my approach for solving this

Approach

I am getting input parameters in the form array of objects liks this

input_json = [{
        "userID":2,
        "companyID":6
    },
    {
        "userID":1,
        "companyID":4
    },
]

After getting the input JSON, I am making two different arrays users array, company's array, user_array will hold all the userIDs, company_array will hold all the companyIDs like this

user_array = input_json.map((elem)=>elem.userID) // which will hold all the company ID

//For the above input_json, the value of `user_array` would be 
  //  `user_array: [2,1]`


company_array = input_json.map((elem)=>elem.userID)
//For the above input_json, the value of `company_array` would be 
  //  `company_array`:[4,4]

After I got both the values of userIDs & companyID separately, I am firing a query on MongoDB using the $in to match id from array
```
db.collection.find({
    userID:{
           $in:user_array 
    },
    accounts.companyID:{
        $in:company_array
    }
})
```
I am not getting the desired results, I want the userID and companyID to be used as an unique key for getting the data.

But what is happening is it is matching the userID in user_array and companyID in company_array.However, I want both userID and companyID to be checked for getting the data.

Any help will be much apreciated :)

Pretty sure you just mean { $or: arr } here. That would be the AND of each "set" of conditions, where as $in applies all different sets of combinations over two values. Show documents you would expect to match if you are still unsure — Neil Lunn
– Neil Lunn, Commented May 3, 2018 at 10:50
Hi Sunil Lulla; to make it really clear what you are trying to do, and what is going wrong, could you paste in an example of the query results you're getting back, and an example of the query results you want to get? — Vince Bowdren
– Vince Bowdren, Commented May 3, 2018 at 11:42
So if one field is in the top of the document and another is in an array with "listOfFields" then how are you supposed to know which one searches the root and which searches the array? In all honesty, the real problem here is you have real data you are not showing us. Trying to express it in an abstract way is simply not coming across in a clear manner. Better to show something more representative of your actual data and a real request, otherwise it's likely any response you get will also be very wrong. — Neil Lunn
– Neil Lunn, Commented May 3, 2018 at 12:16

Community · Accepted Answer · 2020-06-20 09:12:55Z

Just expanding a little on your sample to include the "possible" case that your request array actually asks for multiple users from the same company.

The essential "query" condition is simply a remapping of the input array and using it within an $or query argument. which comes out like:

{
  "$or": [
    {
      "companyID": "6",
      "accounts.userID": "3"
    },
    {
      "companyID": "6",
      "accounts.userID": "2"
    },
    {
      "companyID": "4",
      "accounts.userID": "1"
    }
  ]
}

That will match you the "documents", but the respective "user" information is contained within the "accounts" array. In order to retrieve only those items we need to apply a $filter condition to simply keep those array entries matching the criteria. Then it's really just a matter of using $unwind on the remaining array content and a little document reshaping to put in the desired output format with $project.

The whole generated statement would come out like:

Company.aggregate([
  { '$match': { 
    '$or': [
      { companyID: '6', 'accounts.userID': '3' },
      { companyID: '6', 'accounts.userID': '2' },
      { companyID: '4', 'accounts.userID': '1' }
    ]
  }},
  { '$addFields': { 
    accounts: { 
     '$filter': { 
        input: '$accounts',
        cond: {
          '$or': [ 
            { '$and': [
              { '$eq': [ '$companyID', '6' ] },
              { '$eq': [ '$$this.userID', '3' ] }
            ] },
            { '$and': [
              { '$eq': [ '$companyID', '6' ] },
              { '$eq': [ '$$this.userID', '2' ] }
            ] }, 
            { '$and': [
              { '$eq': [ '$companyID', '4' ] }, 
              { '$eq': [ '$$this.userID', '1' ] }
            ] }
          ]
        }
      }
    }
  }},
  { '$unwind': '$accounts' },
  { '$project': {
    userID: '$accounts.userID',
    companyID: 1,
    preferences: '$accounts.preferences'
  }}
])

The content of the $or for the query and the additional form of $or for the $filter are basically generated from the input array like so:

    let query = {
      $or: input.map(({ userID, companyID }) =>
        ({ companyID, 'accounts.userID': userID }))
    };

    let condition = input.map(({ userID, companyID }) =>
      ({ "$and": [
        { "$eq": ["$companyID", companyID] },
        { "$eq": ["$$this.userID", userID] }
      ]})
    );

And then used as arguments within the rest of the pipeline construction, which is mostly static. Note the usage within the $filter as "cond" requires the "aggregation logical operators" which return a boolean value based on what they test. So they are different from the query operators in form an function.

The same "pairing" applies to each $or condition so that both the "companyID" value as well as the current "userID" value in the accounts array are considered when looking for a match with that combination. Within the $filter it is important to check the "companyID" from outside the array whilst also checking the current array element.

The reason we cannot do this with standard positional $ operator projection is essentially because of the $or condition in the query. There is the additional constraint of "multiple matches" added here for demonstration, but because of the $or in the query, MongoDB cannot determine which of the "set of conditions" actually satisfied element match position for any individual document anyway.

So it does not really matter if you want "one" user to match from each company or "many", since the same aggregation filter needs to be applied in order to extract the correct "user(s)" detail anyway.

A full listing for demonstration follows:

const { Schema } = mongoose = require('mongoose');

const uri = 'mongodb://localhost/test';

mongoose.Promise = global.Promise;
mongoose.set('debug',true);

let input = [
  {
    "userID": 3,
    "companyID": 6
  },
  {
    "userID": 2,
    "companyID": 6
  },
  {
    "userID": 1,
    "companyID": 4
  }
];


// non-strict for testing
const Company = mongoose.model('Company', new Schema({},{ strict: false }));

const log = data => console.log(JSON.stringify(data, undefined, 2));

(async function() {

  try {

    const conn = await mongoose.connect(uri);

    // Clean data to actually be matching strings
    input = input.map(({ userID, companyID }) =>
      ({ userID: userID.toString(), companyID: companyID.toString() }));


    let query = {
      $or: input.map(({ userID, companyID }) =>
        ({ companyID, 'accounts.userID': userID }))
    };
    log(query);

    let condition = input.map(({ userID, companyID }) =>
      ({ "$and": [
        { "$eq": ["$companyID", companyID] },
        { "$eq": ["$$this.userID", userID] }
      ]})
    );
    log(condition);


    let result = await Company.aggregate([
      { "$match": query },
      { "$addFields": {
        "accounts": {
          "$filter": {
            "input": "$accounts",
            "cond": { "$or": condition }
          }
        }
      }},
      { "$unwind": "$accounts" },
      { "$project": {
        "userID": "$accounts.userID",
        "companyID": 1,
        "preferences": "$accounts.preferences"
      }}
    ]);

    log(result);

    mongoose.disconnect();
  } catch(e) {
    console.error(e)
  } finally {
    process.exit()
  }


})()

And this gives output like:

[
  {
    "_id": "5aeaf7c73c3e9de82d91e439",
    "companyID": "4",
    "userID": "1",
    "preferences": [
      {
        "emailNotification": true,
        "smsNotification": true,
        "pushNotification": false,
        "webNotification": false,
        "lastUpdatedBy": "SYSTEM",
        "_id": "5aeaf7c720262a1db759edf7",
        "preferenceID": "6fbd6c-4c56-11e8-842f-0ed5f89f718b",
        "createdAt": "2018-05-03T11:51:35.509Z",
        "updatedAt": "2018-05-03T11:51:35.509Z"
      },
      {
        "emailNotification": true,
        "smsNotification": true,
        "pushNotification": false,
        "webNotification": false,
        "lastUpdatedBy": "SYSTEM",
        "_id": "5aeaf7c720262a1db759edf6",
        "preferenceID": "6fb118-4c56-11e8-842f-0ed5f89f718b",
        "createdAt": "2018-05-03T11:51:35.509Z",
        "updatedAt": "2018-05-03T11:51:35.509Z"
      }
    ]
  },
  {
    "_id": "5aeafe6d3c3e9de82d91e43b",
    "companyID": "6",
    "userID": "2",
    "preferences": [
      {
        "emailNotification": true,
        "smsNotification": true,
        "pushNotification": false,
        "webNotification": false,
        "lastUpdatedBy": "SYSTEM",
        "_id": "5aeafe738b1d5f2057419ca1",
        "preferenceID": "6fbd6c-4c56-11e8-842f-0ed5f89f718b",
        "createdAt": "2018-05-03T12:20:03.987Z",
        "updatedAt": "2018-05-03T12:20:03.987Z"
      },
      {
        "emailNotification": true,
        "smsNotification": true,
        "pushNotification": false,
        "webNotification": false,
        "lastUpdatedBy": "SYSTEM",
        "_id": "5aeafe738b1d5f2057419ca0",
        "preferenceID": "6fb118-4c56-11e8-842f-0ed5f89f718b",
        "createdAt": "2018-05-03T12:20:03.987Z",
        "updatedAt": "2018-05-03T12:20:03.987Z"
      }
    ]
  },
  {
    "_id": "5aeafe6d3c3e9de82d91e43b",
    "companyID": "6",
    "userID": "3",
    "preferences": [
      {
        "emailNotification": true,
        "smsNotification": true,
        "pushNotification": false,
        "webNotification": false,
        "lastUpdatedBy": "SYSTEM",
        "_id": "5aeafe778b1d5f2057419ca4",
        "preferenceID": "6fbd6c-4c56-11e8-842f-0ed5f89f718b",
        "createdAt": "2018-05-03T12:20:07.062Z",
        "updatedAt": "2018-05-03T12:20:07.062Z"
      },
      {
        "emailNotification": true,
        "smsNotification": true,
        "pushNotification": false,
        "webNotification": false,
        "lastUpdatedBy": "SYSTEM",
        "_id": "5aeafe778b1d5f2057419ca3",
        "preferenceID": "6fb118-4c56-11e8-842f-0ed5f89f718b",
        "createdAt": "2018-05-03T12:20:07.062Z",
        "updatedAt": "2018-05-03T12:20:07.062Z"
      }
    ]
  }
]

Which shows we only returned the matching company and user detail combinations from the documents.

NOTE Not sure if with the input sample you gave it was "intentional" that the values are given as "numeric" and not as "strings", where of course they are actually "strings" in the data it needs to match to. There's a simple line of code which is converting the numeric values to strings, which of course is not necessary where both your input types and stored types match already.

Also whilst mongoose would typically "cast" these values under normal query operations to whatever was in the schema, this does not happen with aggregation pipelines. Any conditions you apply for matching within the aggregation pipeline operations requires "you" to cast values to the correct type yourself.

Mongoose does not do this because it cannot make the "presumption" during an aggregation pipeline stage that the data presented at the time is in the same state as what the schema knows about. Aggregation operations are typically about "reshaping documents", and for this reason "schema" does not apply here.

Thanks, it is working perfectly as I wanted. I faced a bit of issue in your solution because of $addFields, because it got added in version 3.4, and I was using 3.2. However, I solved it by ugrading it to 3.6 :)

Collectives™ on Stack Overflow

Using $in on multiple fields in MongoDB

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related