Group on multiple fields

Working with Data
#1

Hi,

I have a MongoDB collection named “histoValues” whose fields are:
_id , ctype, cname, pname, instrument, timestamp, value

I want to select distinct ctype/cname/pname for a given instrument and range of dates (fromDate, toDate).

In SQL, the query I would do is:

SELECT DISTINCT(ctype, cname, pname) 
    FROM histoValues
        WHERE (timestamp >= fromDate and timeStamp <= toDate) 
        AND instrument = 35
        GROUP BY ctype, cname, pname

In MongoDB Compass tool, I firstly tried to group on ctype, without any $match.
In SQL it would be :
SELECT DISTINCT(ctype)
FROM histoValues
GROUP BY ctype

In Compass, I used the $group aggregation operator and entered:

{
_id: “$ctype”
}

In the “output after $group stage” window, there is the following result:

_id: “Axis”

while I have three different ctype values in my collection.

What am I doing wrong ?

And how do I add fields in my $group operator ?

Thanks a lot for your help

#2

:wave:

Hi @Helene_ORTIZ, and welcome to the community forums.

To group on multiple fields in an aggregation, you would make the _id field an object like the following:

{
    $group: {
        "_id": {
            ctype: "$ctype",
            cname: "$cname",
            pname: "$pname"
        },
        ...
    }
}

this will give you a grouping for each unique combination of values in those three fields.

Without seeing the data, query and results it’s hard to say why you only got a single value back if you have three unique values in the dataset for ctype.

#3

Thanks a lot Doug for your answer.
It works fine in Robo3T: the group operator returns 9 documents.
But it does not work in MongoDB Compass tool, which returns only one document.
Please see attached file

screenshot
screenshot1921×1468 180 KB

#4

@Helene_ORTIZ can you turn off Sample Mode inside of Compass? I would guess that all the samples have the same three values and that’s why you’re only getting a single result.

#5

Hi Doug,
I changed “Number of preview documents” in settings panel and now it works fine, like in Robo3T.
Thanks !
The right code is:

db.getCollection(‘histoValues’).aggregate([
{$match: {
$and: [
{instrument: 94},
{timestamp: {$gte: new Date(‘2020-04-01’)}},
{timestamp: {$lte: new Date(‘2020-04-25’)}}
]
}
},
{$group: { _id: {ctype: “$ctype”,
cname: “$cname”,
pname: “$pname”
}
}
}
])

#6

Glad you got things working in Compass now Hélène!