CODEWITHSUNDEEP
mongodb
qff
qff

Reputation: 5912

How do I query by field order in MongoDB?

Let's say I have the following documents in my MongoDB collection outfits:

{ _id: ..., shirt: { type: "T-shirt", size: "M" } }
{ _id: ..., shirt: { type: "Long-sleeve", size: "M" } }
{ _id: ..., shirt: { type: "T-shirt", size: "S" } }
{ _id: ..., shirt: { size: "M", type: "Long-sleeve" } }
{ _id: ..., shirt: { type: "T-shirt", size: "L" } }

How do I get all documents where the size-field is the first field in the embedded shirt-document?

I would also be satisfied with a solution that simply tells me which documents where size is before type in shirt.

Why?

When querying an embedded document in MongoDB, the order of the fields matter. For example:

db.outfits.find({ "shirt": { size: "M", type: "Long-sleeve" } })

will get me just one outfit, even though there are two outfits with a shirt of the type Long-sleeve in size M.

Basically I need to run a query to standardize the field order in my collection, and optimally I would like to fix the few ones that aren't in the right order instead of re-inserting everything.

Reference: https://softwareengineering.stackexchange.com/questions/319468/why-does-mongodb-check-for-order-of-keys-when-matching-embedded-documents

Upvotes: 0

Views: 190

Answers (2)

qff
qff

Reputation: 5912

I ended up using the following:

db.outfits.find({ $where: function() {
  return Object.keys(this.shirt)[0] === 'type'
} })

But I also found the more archaic:

db.outfits.find({ $where: function() {
  return tojsononeline(this)[64] === 't'
} })

Upvotes: 0

dnickless
dnickless

Reputation: 10918

For MongoDB > 3.4.4 you can do this:

db.outfits.aggregate({
    $project: {
        firstField: { // create a new field "firstField"
            $arrayElemAt: [{ // get the first element of...
                $objectToArray: "$shirt" // ... the "shirt" subdocument represented as an array
            }, 0]
        }
    }
}, {
    $match: {
        "firstField.k": "size" // just return the ones we are interested in
    }
})

Also note that you can query like this which will eliminate the problem with the order:

db.outfits.find({ "shirt.size": "M", "shirt.type": "Long-sleeve" })

Upvotes: 1

Related Questions