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?

There is no genreric query for your data-structure. Anyway use db.b.find({$or : [{"Text1.id":'2'}, {"Text2.id":'2'}, {"Text3.id":'2'}]}) — Disposer
– Disposer, Commented Dec 14, 2014 at 18:24
gives me unexpected token : . what do you mean There is no genreric query for your data-structure? Isnt that how you write json. I saw it online in many times — asdsd
– asdsd, Commented Dec 14, 2014 at 18:28
I want for example to write db.myCollection.find({"id":1}) and return text3 and all its components (here there arent but in my object there is) — asdsd
– asdsd, Commented Dec 14, 2014 at 18:38

Disposer · Accepted Answer · 2014-12-14 19:57:26Z

3

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"
        }
    }
}

edited Dec 14, 2014 at 19:57

answered Dec 14, 2014 at 18:48

Disposer

6,3915 gold badges34 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

asdsd Over a year ago

Thank you for help, but isnt there a way (a structure) in which i could extract the data using db.myCollection.find({"Text1.id" : "2"}). I saw it also on the internet as answers

Disposer Over a year ago

as @styvane soukossi answered, yes you can, but you have to know your target field which in this case is "Text1.id"

asdsd Over a year ago

I know, if it was working i would have changed my structure so i will fit,But it didnt work.

Disposer Over a year ago

What didn't work? my solution or @styvane soukossi solution? I tested mine and his(or her ;)) and both working. ( in my solution use db.myCollection.find(..) anstead of db.b.find(..)

Disposer Over a year ago

What is the error?. for me @styvane query produce this doc (query works fine) { "_id" : ObjectId("548de19b1a01c68fab8d67d8"), "Text1" : { "id" : "2" } }

|

Sede · Accepted Answer · 2015-09-07 20:18:49Z

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"
                        ]
                }
        ]
}

Collectives™ on Stack Overflow

Finding JSON objects in mongoDB

2 Answers 2

9 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related