I have a Schema

// mongoose schema
const MySchema = new Schema({ objWithDynamicKeys: { type: Map, of: String } });

const OtherSchema = new Schema({
  limit: Number,
  refToMySchema: { type: Schema.Types.ObjectId, ref: 'MyModel' },
  name: String,
});

Documents of MySchema model look like this

const doc = {
  _id: new ObjectId("some-string"),
  objWithDynamicKeys: {
    "62f74bcfd4aa7ff5c45c7fe3": 2,
    "62f74bcfd4aa7ff5c45c7fe4": 5,
    "62f74bcfd4aa7ff5c45c7fe5": 1,
}

Documents of OtherSchema model look like this

const otherDoc1 = {
  _id: new ObjectId("62f74bcfd4aa7ff5c45c7fe3"),
  limit: 5,
  name: "First",
};
const otherDoc2 = {
  _id: new ObjectId("62f74bcfd4aa7ff5c45c7fe4"),
  limit: 5,
  name: "Second",
};
const otherDoc3 = {
  _id: new ObjectId("62f74bcfd4aa7ff5c45c7fe5"),
  limit: 3,
  name: "Third",
};

I'm building an aggregation that should find all OtherSchema documents whose _id is key in objWithDynamicKeys of MySchema document, where value of objWithDynamicKeys is less than limit of corresponding document.

So after running the aggregation I want to have the following output

[
  {
    _id: new ObjectId("62f74bcfd4aa7ff5c45c7fe3"), // doc1
    limit: 5,
    name: "First",
  },
  {
    _id: new ObjectId("62f74bcfd4aa7ff5c45c7fe5"), // doc3
    limit: 5,
    name: "Third",
  },
];

If objWithDynamicKeys was an array, it wouldn't be so hard

{
  $lookup: {
    from: 'othercollection',
    localField: 'objWithDynamicKeys',
    foreignField: '_id',
    as: 'out',
    pipeline: [
      {
        $match: {
          $expr: {
            $lt: ['$field_from_somewhere', '$limit'],
          },
        },
      },
    ],
  },
},

How can I do that? Is it even possible thing to do?

CodePudding user response：

1. $set - Set objWithDynamicKeys to convert key-value pair to array via $objectToArray.

2. $unwind - Desconstruct objWithDynamicKeys array to multiple documents.

3. $lookup- Join with *Other schema collection* by matching_idand less thanlimitconditions and returns the result asout` array.

4. $match - Filter the documents with out is not an empty array.

5. $replaceWith - Replace the input document with taking the first document of out array.

MySchema.aggregate([
  {
    $set: {
      objWithDynamicKeys: {
        $objectToArray: "$objWithDynamicKeys"
      }
    }
  },
  {
    $unwind: "$objWithDynamicKeys"
  },
  {
    $lookup: {
      from: "/* Other Schema collection */",
      let: {
        key: {
          $toObjectId: "$objWithDynamicKeys.k"
        },
        value: "$objWithDynamicKeys.v"
      },
      as: "out",
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                {
                  $eq: [
                    "$$key",
                    "$_id"
                  ]
                },
                {
                  $lt: [
                    "$$value",
                    "$limit"
                  ]
                }
              ]
            }
          }
        }
      ]
    }
  },
  {
    $match: {
      out: {
        $ne: []
      }
    }
  },
  {
    $replaceWith: {
      $first: "$out"
    }
  }
])

Sample Mongo Playground