I have a db like this

[
    {
        _id: ObjectId("1"),
        name: "John Doe",
        age: 25,
        address: {
            street: "123 Main St",
            city: "New York",
            state: "NY",
            zip: "10001"
        },
        images : [
            {
                url: "http://www.example.com/image1.jpg",
                caption: "Image 1"
                type: "jpg"
            },
            {
                url: "http://www.example.com/image2.jpg",
                caption: "Image 2"
                type: "png"
            },
            {
                url: "http://www.example.com/image3.jpg",
                caption: "Image 3"
                type: "svg"
            },
            {
                url: "http://www.example.com/image4.jpg",
                caption: "Image 4"
                type: "jpg"
            }
        ]
    },
    {
        _id: ObjectId("2"),
        name: "Jack",
        age: 22,
        address: {
            street: "132 lake",
            city: "New York",
            state: "NY",
            zip: "10001"
        },
        images : [
            {
                url: "http://www.example.com/image212.jpg",
                caption: "Image 212"
                type: "png"
            },
            {
                url: "http://www.example.com/image34.jpg",
                caption: "Image 34"
                type: "svg"
            }
        ]
    },
    {
         _id: ObjectId("3"),
        name: "Random name",
        age: 27,
        address: {
            street: "Lajpat nagar",
            city: "New Delhi",
            state: "Delhi",
            zip: "121002"
        },
        images : [
            {
                url: "http://www.example.com/image5.jpg",
                caption: "Image 5"
                type: "jpg"
            },
            {
                url: "http://www.example.com/image7.jpg",
                caption: "Image 7"
                type: "png"
            },
            {
                url: "http://www.example.com/image12.jpg",
                caption: "Image 12"
                type: "svg"
            },
            {
                url: "http://www.example.com/image8.jpg",
                caption: "Image 8"
                type: "jpg"
            }
        ]
    }
]

I want to calculate total number of records in which images array has more than one 'jpg' type image object in them.

For above DB it should be 2 i.e. for Object Id 1 & 2. As in 1, there are 2 objects in the images array with type 'jpg'. There are no such objects in Record 2. While the 3rd Record also have 2 such objects.

How to do query in mongoDB, I have tried using $size & $unwind & $group operator but failed to do it.

CodePudding user response：

you could do something as follows :

db.collection.aggregate([
  {
    $match: {
      $expr: {
        "$gte": [
          {
            $size: {
              "$filter": {
                "input": "$images",
                "as": "img",
                "cond": {
                  "$eq": [
                    "$$img.type",
                    "jpg"
                  ]
                }
              }
            }
          },
          1
        ]
      }
    }
  },
  {
    "$count": "res"
  },
  
])

See on mongoplayground https://mongoplayground.net/p/LCCcCNkyzZX

What i do here is a $match to get all document where length of images (filtered by type = jpg) is >=1.

Then i use the $count operator to retrieve the number of document matched previously.

CodePudding user response：

I think I have a solution for you. I have used $unwind, $match, $group and $sum. Here is my code below =>
Code

db.collection.aggregate([
  {
    "$unwind": "$images"
  },
  {
    "$match": {
      "images.type": "jpg"
    }
  },
  {
    "$group": {
      "_id": "$_id",
      "total": {
        "$sum": 1
      }
    }
  }
])

Output

[
  {
    "_id": "1",
    "total": 2
  },
  {
    "_id": "3",
    "total": 2
  }
]

Note: Please check the link : https://mongoplayground.net/p/zVi4MlOc-Fs