how to group by nested documents in mongoDB-CodePudding

I've started to learning mongoDB recently and doing some examples to improve myself. I have a problem, I can not imagine how can I do this in mongoDB or is this really possible to get. So I need your help.

Firstly, I have a collection like that;

/* 1 */
{
    "_id" : ObjectId("62372c37ea4cbb005e97ec1c"),
    "CreatedTime" : ISODate("2022-03-20T13:29:27.456Z"),
    "UpdatedTime" : Date(-62135596800000),
    "Name" : "Name1",
    "Surname" : "Surname1",
    "Company" : "Company1",
    "ContactInformation" : null
}

/* 2 */
{
    "_id" : ObjectId("62372c37ea4cbb005e97ec1d"),
    "CreatedTime" : ISODate("2022-03-20T13:29:27.456Z"),
    "UpdatedTime" : Date(-62135596800000),
    "Name" : "Name2",
    "Surname" : "Surname2",
    "Company" : "Company2",
    "ContactInformation" : [ 
        {
            "InfoType" : 1,
            "Info" : " 905554443322"
        }, 
        {
            "InfoType" : 3,
            "Info" : "İstanbul"
        }
    ]
}

/* 3 */
{
    "_id" : ObjectId("62372c37ea4cbb005e97ec1e"),
    "CreatedTime" : ISODate("2022-03-20T13:29:27.456Z"),
    "UpdatedTime" : Date(-62135596800000),
    "Name" : "Name3",
    "Surname" : "Surname3",
    "Company" : "Company3",
    "ContactInformation" : [ 
        {
            "InfoType" : 1,
            "Info" : " 905554443301"
        }, 
        {
            "InfoType" : 1,
            "Info" : " 905554443302"
        }, 
        {
            "InfoType" : 3,
            "Info" : "Kastamonu"
        }
    ]
}

/* 4 */
{
    "_id" : ObjectId("62372c37ea4cbb005e97ec1f"),
    "CreatedTime" : ISODate("2022-03-20T13:29:27.456Z"),
    "UpdatedTime" : Date(-62135596800000),
    "Name" : "Name4",
    "Surname" : "Surname4",
    "Company" : "Company4",
    "ContactInformation" : [ 
        {
            "InfoType" : 3,
            "Info" : "Kastamonu"
        }
    ]
}

The InfoType: 1 - Phone Number, 3 - Location(City, Country etc.) The Info is the value of InfoTypes

Then I want to get a report has with three values;

The Location information (If the document has nested document with InfoType: 3)
The count of records where they belongs the location
The phone number count where belong the location

expected output:

{
 location: "İstanbul",
 recordCount: 1,
 phoneNumCount: 1
},
{
 location: "Kastamonu",
 recordCount: 2,
 phoneNumCount: 2
}

first two conditions are ok, I can get them but the third one of I couldn't.

Thank you all for your helpings

CodePudding user response：

Try the following pipeline:

Find country of the user and add that as a field using $addFields. Also add ContactInformation as an empty array if null.
Use $group to group documents by user's country. Then use $count to count number of records and $sum to phoneNumbers of that country.

[
  {
    $addFields: {
      country: {
        $arrayElemAt: [
          {
            $filter: {
              input: "$ContactInformation",
              as: "info",
              cond: {
                $eq: [
                  "$$info.InfoType",
                  3
                ]
              }
            }
          },
          0
        ]
      },
      ContactInformation: {
        $ifNull: [
          "$ContactInformation",
          []
        ]
      }
    }
  },
  {
    $group: {
      _id: "$country.Info",
      recordCount: {
        $count: {}
      },
      phoneNumCount: {
        $sum: {
          $size: {
            $filter: {
              input: "$ContactInformation",
              as: "info",
              cond: {
                $eq: [
                  "$$info.InfoType",
                  1
                ]
              }
            }
          }
        }
      }
    }
  }
])

Mongo Playground