Finding JSON objects in mongoDB

Question

I'm trying to find objects using the built it queries and It just doesn't work.. My JSON file is something like this:

{   "Text1":
    {
        "id":"2"
    },
    "Text2":
    {
       "id":"2,3"
    },
    "Text3":
    {
       "id":"1"
    }
}

And I write this db.myCollection.find({"id":2}) And it doesn't find anything. When I write db.myCollection.find() it shows all the data as it should.

Anyone knows how to do it correctly?

Answer 1

Since your are query specify a field in a subdocument this is what will work. see .find() documentation.

db.myCollection.find({"Text1.id" : "2"}, {"Text1.id": true})
{ "_id" : ObjectId("548dd798e2fa652e675af11d"), "Text1" : { "id" : "2" } }

---

If the query is on "Text1" or "Text2" the best thing to do here as mention in the accepted answer is changing you document structure. This can be easily done using the "Bulk" API.

var bulk = db.mycollection.initializeOrderedBulkOp(),
    count = 0;

db.mycollection.find().forEach(function(doc) { 
    var pair = []; 
    for(var key in doc) {     
        if(key !== "_id") { 
            var id = doc[key]["id"].split(/[, ]/); 
            pair.push({"key": key, "id": id}); 
        }
    } 
    bulk.find({"_id": doc._id}).replaceOne({ "pair": pair });  
    count++; if (count % 300 == 0){ 
        // Execute per 300 operations and re-Init
        bulk.execute();     
        bulk = db.mycollection.initializeOrderedBulkOp();
    } 
})

// Clean up queues
if (count % 300 != 0 )
    bulk.execute();  

Your document now look like this:

{
        "_id" : ObjectId("55edddc6602d0b4fd53a48d8"),
        "pair" : [
                {
                        "key" : "Text1",
                        "id" : [
                                "2"
                        ]
                },
                {
                        "key" : "Text2",
                        "id" : [
                                "2",
                                "3"
                        ]
                },
                {
                        "key" : "Text3",
                        "id" : [
                                "1"
                        ]
                }
        ]
}

---

Running the following query:

db.mycollection.aggregate([
    { "$project": { 
        "pair": {
            "$setDifference": [
                { "$map": {
                    "input": "$pair", 
                    "as": "pr", 
                    "in": { 
                        "$cond": [
                            { "$setIsSubset": [ ["2"], "$$pr.id" ]}, 
                            "$$pr", 
                            false
                        ]
                    }
                }}, 
                [false]
            ]
        }
    }}
])

returns:

{
        "_id" : ObjectId("55edddc6602d0b4fd53a48d8"),
        "pair" : [
                {
                        "key" : "Text1",
                        "id" : [
                                "2"
                        ]
                },
                {
                        "key" : "Text2",
                        "id" : [
                                "2",
                                "3"
                        ]
                }
        ]
}

Answer 2

Its hard to change the data-structure but as you want just your matching sub-document and you don't know where is your target sub-document (for example the query should be on Text1 or Text2 , ...) there is a good data structure for this:

{
    "_id" : ObjectId("548dd9261a01c68fab8d67d7"),
    "pair" : [ 
        {
            "id" : "2",
            "key" : "Text1"
        }, 
        {
            "id" : [ 
                "2", 
                "3"
            ],
            "key" : "Text2"
        }, 
        {
            "id" : "1",
            "key" : "Text3"
        }
    ]
}

and your query is:

db.myCollection.findOne({'pair.id' : "2"} , {'pair.$':1, _id : -1}).pair // there is better ways (such as aggregation instead of above query)

as result you will have:

{
    "0" : {
        "id" : "2",
        "key" : "Text1"
    }
}

Update 1 (newbie way)

If you want all the document not just one use this

var result = [];
db.myCollection.find({'pair.id' : "2"} , {'pair.$':1, _id : -1}).forEach(function(item)
{
    result.push(item.pair);
});

// the output will be in result

Update 2

Use this query to get all sub-documents

db.myCollection.aggregate
(
   { $unwind: '$pair' },
   { $match : {'pair.id' : "2"} }
).result

it produce output as

{
    "0" : {
        "_id" : ObjectId("548deb511a01c68fab8d67db"),
        "pair" : {
            "id" : "2",
            "key" : "Text1"
        }
    },
    "1" : {
        "_id" : ObjectId("548deb511a01c68fab8d67db"),
        "pair" : {
            "id" : [ 
                "2", 
                "3"
            ],
            "key" : "Text2"
        }
    }
}