MongoDB find query with multiple query field using $or

Question

I am looking for a Mongo query for finding data in the following document.

{
    "key1": [{
        "subkey1": ["america.south.gas"],
        "subkey2": ["9898989898"]
    }],

    "key2": [{
            "subkey1": ["america"],
            "subkey2": ["hsadjsahjsahdjsah879878u9"]
        },
        {
            "subkey1": ["america.south.gas","america"],
            "subkey2": ["hsadjsahjsahdjsah879878u9"]
        },
        {
            "subkey1": ["america.south.#"],
            "subkey2": ["sjadkjsahdkjsahdj989s89d8sa98d9sa"]
        }]
}

I want only subkey2 only of above mentioned document as following output:

"subkey2": ["hsadjsahjsahdjsah879878u9"] 
"subkey2": ["sjadkjsahdkjsahdj989s89d8sa98d9sa"]

Now I want to fetch data with following query:-

db.collectionName.find({$or:[ 
        {"key2.subkey1": "america.south.gas"},   
        {"key1.subkey1": "america.south.#"} 
    ]},
    {"_id": 0, "key2.subkey2.$": 1}
);

But it is showing me this error:

{ 
    "waitedMS" : NumberLong(0), 
    "ok" : 0, 
    "errmsg" : "Executor error during find command: BadValue: positional operator (key1.$) requires corresponding field in query specifier", 
    "code" : 96 
}

Any idea how can I achieve this for getting specific field with multiple query field in Mongo find operation??

"But it is showing me error" ... could you update your question to show us this error? Also, this kind of question is often made clearer by including an example document showing exacty what output you are expecting. This is often the least ambiguous way of expressing your requirements. — glytching
– glytching, Commented Feb 14, 2018 at 14:41
The question includes the java tag, is this relevant? Are you trying to express this query using the Mongo Java driver? Or using the Mongo shell? — glytching
– glytching, Commented Feb 14, 2018 at 14:47
I am trying it using Mongo shell and through java also..and i want only subkey2 only of above mentioned document as following output:- "subkey2": ["hsadjsahjsahdjsah879878u9"] "subkey2": ["sjadkjsahdkjsahdj989s89d8sa98d9sa"] — Prashant Bhagat
– Prashant Bhagat, Commented Feb 14, 2018 at 15:39
and the error, which i am getting is :- db.wildcards.find({$or:[{"key1.subkey1":"america.south.gas"},{"key1.subkey1":"america.south.#"}]},{"_id":0,"key1.subkey2.$":1}) Error: error: { "waitedMS" : NumberLong(0), "ok" : 0, "errmsg" : "Executor error during find command: BadValue: positional operator (key1.$) requires corresponding field in query specifier", "code" : 96 } — Prashant Bhagat
– Prashant Bhagat, Commented Feb 14, 2018 at 15:44

glytching · Accepted Answer · 2018-02-14 15:59:26Z

1

This error ...

Error: error: { "waitedMS" : NumberLong(0), "ok" : 0, "errmsg" : "Executor error during find command: BadValue: positional operator (key1.$) requires corresponding field in query specifier", "code" : 96 }

.. is caused by this projection:

"key2.subkey2.$": 1

Soecifically, the positional operator: $.

According to the comments above, you ...

want only subkey2 only of above mentioned document as following output:- "subkey2": ["hsadjsahjsahdjsah879878u9"] "subkey2": ["sjadkjsahdkjsahdj989s89d8sa98d9sa"]

The following command ...

db.collection.aggregate([
    {$unwind:'$key2'}, 
    // find the documents having key2.subkey1 = 'america.south.#' or key2.subkey1 = 'america.south.gas'
    {$match:{'key2.subkey1':{$in:['america.south.#','america.south.gas']}}},
    // limit the output to only those key2.subkey2 entries for which the associated key2.subkey1 match the previous $match clause
    {$group:{_id:0,'subkey2':{$push:'$key2.subkey2'}}},
    // only return subkey2
    {$project: {_id: 0, 'subkey2':1}}
])

... will return:

{
    "subkey2" : [ 
        [ 
            "hsadjsahjsahdjsah879878u9"
        ], 
        [ 
            "sjadkjsahdkjsahdj989s89d8sa98d9sa"
        ]
    ]
}

answered Feb 14, 2018 at 15:59

glytching

48.5k11 gold badges127 silver badges129 bronze badges

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

5 Comments

Prashant Bhagat Over a year ago

Thank @glytching . It works for me.One more thing i am getting this response via java-script code:-{_batch: [{subkey2: [[hsadjsahjsahdjsah879878u9, sadsadsadsafdsafsafd], [hsadjsahjsahdjsah879878u9, sadsadsadsafdsafsafd]]]}], _cursor: {}}. Can i ignore "_batch" and "_cursor" keys from response...

glytching Over a year ago

That looks like whatever JS MongoDB driver you are using wraps the raw response in a document containing _batch and _cursor> I'm guessing that the JS MongoDB driver allows you to interrogate the wrapped response to only read the _batch attribute.

Prashant Bhagat Over a year ago

Thanks for your response. Also i am executing same query with 5000 records, this is taking approx 2000ms to retrieve data. How better can i optimize that??

glytching Over a year ago

Run you query with .expain() and review the results to see why it is taking so long.

Prashant Bhagat Over a year ago

response:-{"waitedMS" : NumberLong(0), "stages" : [ "$cursor" : {"query" : {}, "queryPlanner" : {"plannerVersion" : 1, "namespace" : "admin.bulkData", "indexFilterSet" : false, "parsedQuery" : { "$and" : [ ] }, "winningPlan" : { "stage" : "COLLSCAN", "filter" : { "$and" : [ ] }, "direction" : "forward" }, "rejectedPlans" : [ ] }}},"ok" : 1}

Anil Singh · Accepted Answer · 2018-02-21 18:46:46Z

0

@glytching, If i need output like that as below given then what need to change in query and also no duplicate output value.

{ "subkey2" : [ "hsadjsahjsahdjsah879878u9","sjadkjsahdkjsahdj989s89d8sa98d9sa"]

}

answered Feb 21, 2018 at 18:46

Anil Singh

132 silver badges8 bronze badges

Collectives™ on Stack Overflow

MongoDB find query with multiple query field using $or

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related