Mongodb: Query the size of nested arrays-CodePudding

I have the following Schema:

Schema({
caller_address: {
    type: String,
    required: true,
},
traces: [[{
    type: mongoose.Schema.Types.ObjectId,
    ref: 'Call',
}]]

});

And I would like to retrieve only the objects that have traces with the Calls amount bigger than a specified number. In other words, the size of at least one nested array of traces should be bigger than a specified number. I'm trying to use $elemMatch and $size, but no success. For now, I have this code:

CallerTraces.find({ 'traces' : { $elemMatch: { $size : { $gt: minTraceSize } }}})

Where minTraceSize is an int.

Could you guys help me? I would really appreciate it!

CodePudding user response：

Thanks for the sample data. My answer will be a raw MQL solution, not a mongoose solution, so some translation will be required.

I was able to insert two documents based on your comments in your post. I had to change the ObjectId of one of the two sample documents because your samples had the same primary key value and was generating a duplicate key exception.

Insert Sample Data

db.CallerTraces.insert(
{
  "_id": ObjectId("6175e7ecc62cff004462d4a6"),
  "traces": [
    [
      ObjectId("6175e7ecc62cff004462d4a4")
    ]
  ],
  "caller_address": "0x4e204793bc4b8acee32edaf1fbba1f3ea45f7990"
})


db.CallerTraces.insert(
{
  "_id": ObjectId("6175e7ecc62cff004462d4a7"),
  "traces": [
    [
      ObjectId("6175e7ecc62cff004462d4a4"),
      ObjectId("6175e7ecc62cff004462d4a4")
    ],
    [
      ObjectId("6175e7ecc62cff004462d4a4")
    ]
  ],
  "caller_address": "0x4e204793bc4b8acee32edaf1fbba1f3ea45f7990"
})

If I want to find records having more than 0 items in the array traces I can issue the following:

Find more than zero traces

db.CallerTraces.find({ $expr: { $gt: [ { $size: "$traces" }, 0 ] } })

This returns the following:

Enterprise replSet [primary] barrydb> db.CallerTraces.find({ $expr: { $gt: [ { $size: "$traces" }, 0 ] } })
[
  {
    _id: ObjectId("6175e7ecc62cff004462d4a6"),
    traces: [ [ ObjectId("6175e7ecc62cff004462d4a4") ] ],
    caller_address: '0x4e204793bc4b8acee32edaf1fbba1f3ea45f7990'
  },
  {
    _id: ObjectId("6175e7ecc62cff004462d4a7"),
    traces: [
      [
        ObjectId("6175e7ecc62cff004462d4a4"),
        ObjectId("6175e7ecc62cff004462d4a4")
      ],
      [ ObjectId("6175e7ecc62cff004462d4a4") ]
    ],
    caller_address: '0x4e204793bc4b8acee32edaf1fbba1f3ea45f7990'
  }
]

Find more than 1 trace

If instead I want to find more than one trace I simply alter the query slightly:

db.CallerTraces.find({ $expr: { $gt: [ { $size: "$traces" }, 1 ] } })

... and this returns with the following results:

Enterprise replSet [primary] barrydb> db.CallerTraces.find({ $expr: { $gt: [ { $size: "$traces" }, 1 ] } })
[
  {
    _id: ObjectId("6175e7ecc62cff004462d4a7"),
    traces: [
      [
        ObjectId("6175e7ecc62cff004462d4a4"),
        ObjectId("6175e7ecc62cff004462d4a4")
      ],
      [ ObjectId("6175e7ecc62cff004462d4a4") ]
    ],
    caller_address: '0x4e204793bc4b8acee32edaf1fbba1f3ea45f7990'
  }
]

Conclusion

When attempting to evaluate the length of the array within the query processor we must elect to use the $eval option as the syntax for MQL does not consider your use case. The $eval is somewhat of a catch-all option for things that do not fit nicely in the MQL framework.

UPDATE #1 OP introduced additional requirements. Rather than look at the count of the array, we must consider the count of the array within the array (nested inner array). Since the find() method with the $expr cannot evaluate nested arrays we must instead use the aggregation framework and unwind the outer array. This example stores the original form in a new field called original then replaces root after all the evaluation is complete. Since unwinding can result in duplicates in the pipeline we finalize with a $group to suppress duplicates.

Solution

db.CallerTraces.aggregate([
    {
        $addFields: {
            "original._id": "$_id",
            "original.traces": "$traces",
            "original.caller_address": "$caller_address"
        }
    },
    {
        $unwind: "$traces"
    },
    {
        $match: { $expr: { $gt: [ { $size: "$traces" }, 1 ] } }
    },
    {
        $replaceRoot: { newRoot: "$original" }
    },
    {
        $group:
        {
            _id: "$_id",
            traces: { "$first": "$traces" },
            caller_address: { "$first": "$caller_address" }
        }
    }
])