$elemMatch with distinct

Question

I have some problems with distinct queries.

db.sessions.distinct("tests.device_serial")
[
        "",
        "5b34f4bf9854a",
        "5b34f4bf98664",
        "5b34f4bf98712",
        "5b34f4bf9876b",
        "5b34f4bf987c6"
]

I don't want to get the result with empty strings. I tried to run query:

 db.sessions.distinct("tests.device_serial", {"tests.device_serial" : {$ne: ""}})
[ ]

Why I got empty array? Where is my mistake?

this shoud work db.sessions.distinct('{"tests.device_serial":{$ne: ""}}'); — Eric, Jul 04 '18 at 22:33
db.sessions.distinct('{"tests.device_serial":{$ne: ""}}') [ ] Same problem. MongoDB shell version v3.6.3. — Alexander Dobromilskiy, Jul 04 '18 at 22:54
it would be better if you post your sample data because from here it seems to be ok — Ashh, Jul 05 '18 at 11:47
@anthony-winzlet looks like this [{ "obj_lens_inspection_mask": 6, "tests": [{ "device_serial": "5acccdf25c089", "firmware_version": "3.1.00.121" }] }, { "obj_lens_inspection_mask": 3, "tests": [{ "device_serial": "5acccdf25c57a", "firmware_version": "3.1.00.121" }, { "device_serial": "", "firmware_version": "" }, { "device_serial": "5acccdf25c5de", "firmware_version": "3.1.00.121" }] }] — Alexander Dobromilskiy, Jul 05 '18 at 14:00

score 0 · Accepted Answer · answered Jul 05 '18 at 08:32

Guessing tests.device_serial is an array, here's your mistake :

 db.sessions.distinct("tests.device_serial", {"tests.device_serial" : {$ne: ""}})

Query in your distinct command is filtering documents where the array 'tests' contains a field named device_serial with a value of "", and not only the fields in array.

To achieve what you want, you can use aggregation framework, unwind array to multiple documents, filter and group by null with an $addToSet command to get distinct values.

Here's the query :

db.sessions.aggregate(
    [
        {
            $unwind: {
                path : "$tests"
            }
        },
        {
            $match: {
            "tests.device_serial":{$ne:""}
            }
        },
        {
            $group: {
              "_id":null,
                "device_serials":{$addToSet:"$tests.device_serial"}
            }
        },
    ]
);

$elemMatch with distinct

1 Answers1

Linked