MongoDB: How to find documents by value?

Question

Two documents containing ObjectId("6148a371c13a6a0be492ebf4")

Document 1

  {
           "_id" : ObjectId("6144f66fb9543917f96fc"),
           "refId" : "ford",
            "template" : "6144f61cb96d772317f96f9",
            "fieldValues" : {
                "PDV" : [ 
                    "6126938cd24a8aa3d37b4992", 
                    ObjectId("6148a371c13a6a0be492ebf4")
                ]
            },
            "group" : ObjectId("6144f66fb96d7731917f96fd"),
            "createdAt" : ISODate("2021-09-17T20:11:27.440Z"),
            "updatedAt" : ISODate("2021-09-20T15:06:26.146Z"),
            "__v" : 0
  }

Document 2

 {
            "_id" : ObjectId("6144f66fb96d77rr3217f96fc"),
            "refId" : "CCM",
            "template" : "6144f613296d7731917f96f9",
            "fieldValues" : {
                "DDB" : [ 
                    "6126938cd2448aa3d37b4992", 
                    "5443938cd2448aa3d37b4992", 
                    ObjectId("6148a371c13a6a0be492ebf4"),
                ]
            },
            "group" : ObjectId("6144f66fb96de431917f96fd"),
            "createdAt" : ISODate("2021-09-17T20:11:27.440Z"),
            "updatedAt" : ISODate("2021-09-20T15:06:26.146Z"),
            "__v" : 0
 }

ObjectId that we looking for is always inside fieldValues but instead of PDV or DDB we will always have the different naming.

So we can't use this type of query: db.getCollection('products').find({"fieldValues.PDV":ObjectId('6148a371c13a6a0be492ebf4')})

PS. This query should work only on DB, we can't afford to query all products and do calculation on backend there might to be a millions of products.

What are the exact conditions? May `ObjectId("6148a371c13a6a0be492ebf4")` appear **anywhere** in the document? Having an array (PDV or DDB) with elements of different data types will make it rather complicated. — Wernfried Domscheit, Sep 28 '21 at 06:51
Thanks Wernfried Domscheit, good point, I just updated my question. — Qui-Gon Jinn, Sep 28 '21 at 15:37
"we can't afford to query all products and do calculation" - that's exactly what you did with your query. — Wernfried Domscheit, Sep 28 '21 at 18:23
Wernfried Domscheit Yes, I removed this query example because it is wrong and just confusing people. — Qui-Gon Jinn, Sep 28 '21 at 19:55

score 1 · Accepted Answer · answered Sep 28 '21 at 18:22

1

You can use this one:

db.collection.aggregate([
  {
    $set: {
      kv: { $first: { $objectToArray: "$fieldValues" } }
    }
  },
  { $match: { "kv.v": ObjectId("6148a371c13a6a0be492ebf3") } },
  { $unset: "kv" }
])

Mongo Playground

answered Sep 28 '21 at 18:22

Wernfried Domscheit

54,457
9
76
110

Wernfried Domscheit thank you! This is a great solution! – Qui-Gon Jinn Sep 28 '21 at 19:51

score 0 · Answer 2 · answered Sep 28 '21 at 03:46

0

db.products.find({'_id': ObjectId("6148a371c13a6a0be492ebf4")})

The mistake in your code is that you used key instead of _id.

This way of writing it is much easier on the fingers though.

answered Sep 28 '21 at 03:46

Neptune

40
1
6

In your case _id is the key, but I don't have key in this array. Your solution will not working in this case. – Qui-Gon Jinn Sep 28 '21 at 03:51

score 0 · Answer 3 · answered Sep 28 '21 at 04:06

0

You'd think a solution like this would work but one reason why this may not is because you're trying to use === on an object. If you refer to this thread, it might help if you use .equals() instead of ===.

answered Sep 28 '21 at 04:06

Derek Kim

190
2
11

MongoDB: How to find documents by value?

3 Answers3