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 addContactInformation
as an empty array ifnull
. - 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
]
}
}
}
}
}
}
}
])