I have a document like this:

{
  id: string,
  subDocCount: number,
  subDocs: [
    {
      id: string (not an ObjectId),
      dateField: Date,
      booleanField: boolean
    }
  ]
}

I want to set the subDocCount field to the count of subDocs where:

• booleanField is false
• AND (dateField is null OR dateField is in the past)
• AND subDoc id is not in given array of ids

I have tried with this updateMany query but have not been able to figure out the "not in array" part (mongoosejs by the way):

  await docs.updateMany(
    {},
    [
      {
        $set: {
          subDocCount: {
            $size: {
              $filter: {
                input: '$subDocs',
                as: 'subDoc',
                cond: {
                  $and: [
                    { $eq: ['$$subDoc.booleanField', false] },
                    {
                      $or: [
                        { $eq: ['$$subDoc.dateField', null] },
                        { $lt: ['$$subDoc.dateField', startOfToday()] }
                      ]
                    },
                    // this part doesn't work, but doesn't cause an error
                    { $not: { $in: ["$$subDoc.id", excludedIds] } }
                  ]
                }
              }
            }
          }
        }
      }
    ]
  )

Am I able to somehow use a "not in array" type of clause in the filter here?

EDIT

The issue seemed to be with the matching clause I was using in the code, which was omitted here in the example as I thought it was not the issue. I was matching using a string id instead of an ObjectId. I thought this was not the issue because it seems these string ids work when querying through various methods.

I have a document like this:

{
  id: string,
  subDocCount: number,
  subDocs: [
    {
      id: string (not an ObjectId),
      dateField: Date,
      booleanField: boolean
    }
  ]
}

I want to set the subDocCount field to the count of subDocs where:

• booleanField is false
• AND (dateField is null OR dateField is in the past)
• AND subDoc id is not in given array of ids

I have tried with this updateMany query but have not been able to figure out the "not in array" part (mongoosejs by the way):

  await docs.updateMany(
    {},
    [
      {
        $set: {
          subDocCount: {
            $size: {
              $filter: {
                input: '$subDocs',
                as: 'subDoc',
                cond: {
                  $and: [
                    { $eq: ['$$subDoc.booleanField', false] },
                    {
                      $or: [
                        { $eq: ['$$subDoc.dateField', null] },
                        { $lt: ['$$subDoc.dateField', startOfToday()] }
                      ]
                    },
                    // this part doesn't work, but doesn't cause an error
                    { $not: { $in: ["$$subDoc.id", excludedIds] } }
                  ]
                }
              }
            }
          }
        }
      }
    ]
  )

Am I able to somehow use a "not in array" type of clause in the filter here?

EDIT

The issue seemed to be with the matching clause I was using in the code, which was omitted here in the example as I thought it was not the issue. I was matching using a string id instead of an ObjectId. I thought this was not the issue because it seems these string ids work when querying through various methods.

• javascript
• mongodb
• mongoose
• mongodb-query

• 1 Btw why check if past date in 'dateField is not null OR dateField is in the past' since not null means any date so the past check becomes redundant or iam I missing something – cmgchess Commented Mar 20 at 9:26
• @cmgchess Yeah, I was thinking the same. Based on their pipeline, I think the "AND (dateField is not null OR dateField is in the past)" was supposed to be "AND (dateField is null OR dateField is in the past)". – aneroid Commented Mar 20 at 9:29
• @cmgchess yes sorry that was a mistake when I wrote out the question. I updated it now. It should be "AND date is null OR in the past". – ThriceGood Commented Mar 20 at 9:31
• 1 Btw couldn't reproduce mongoplayground/p/6oozcUM9x1s – cmgchess Commented Mar 20 at 10:14
• 1 @aneroid Ok I managed to get a version working through MongooseJS, the problem seems to be with my match clause (omitted from the example). I am using a string id in the match when it should be an ObjectId. Even though querying seems to sometimes work with these string ids. – ThriceGood Commented Apr 2 at 12:14

1 Answer 1

The issue seemed to be with the matching clause I was using in the code, which was omitted here in the example as I thought it was not the issue. I was matching using a string id instead of an ObjectId. I thought this was not the issue because it seems these string ids work when querying through various methods.