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.
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.
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
@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.