Find document with array that consists exclusively particular documents in MongoDB

Question

I have collection path_test with 2 documents in it

Document 1

{
  "_id" : 1,
  "tpc" : 5,
  "path" : [ 
    {
      "nids" : [ 0, 10, 11 ],
      "ctc" : 2
    }, 
    {
      "nids" : [ 0, 10 ],
      "ctc" : 2
    }, 
    {
      "nids" : [ 0, 10, 21 ],
      "ctc" : 1
    }
  ]
}

Document 2

{
  "_id" : 2,
  "tpc" : 5,
  "path" : [ 
    {
      "nids" : [ 0, 10, 110 ],
      "ctc" : 1
    }, 
    {
      "nids" : [ 0, 10, 11 ],
       "ctc" : 2
    }, 
    {
      "nids" : [ 0, 5 ],
      "ctc" : 2
    }
  ]
}

What I'm trying to get as a result are documents with path array in which all elements have nids like [0, 10, *]. Order is important, so [10, 0, *] will be wrong. It should find Document 1, but not Document 2. Was hoping I can resolve this with a query, before I start using map-reduce or aggregation.

This is what I've tried so far

Query1

db.getCollection('path_test').find( {
  "path": { $not: { $elemMatch: { "nids.0": { $nin: [0] }, "nids.1": { $nin: [10] } } } }
});

Query 2

db.getCollection('path_test').find( {
  "path.nids": { $not: { $elemMatch: { $nin: [0, 10] } } }
});

but both queries give me results where only 0 is in or where only 10 is in, but I need both and in that exact order.

Is that possible?

Hope you are aware of this. $elemMatch will return only one record that matches the query. — Shrabanee
– Shrabanee, Commented Aug 5, 2016 at 9:01
@Shrabanee Thanks for pointing that out. But I understood it will match at least one element that contains that field. — adpap
– adpap, Commented Aug 5, 2016 at 9:07
@Shrabanee docs.mongodb.com/manual/reference/operator/query/elemMatch — adpap
– adpap, Commented Aug 5, 2016 at 9:40
@Shrabanee Your suggestion is wrong. According to documentation, the $elemMatch operator matches documents that contain an array field with at least one element that matches all the specified query criteria. It will not return only one record in general case, it will return record, if at least one element in its array field matches all the specified query criteria. — Mr Tarsa
– Mr Tarsa, Commented Aug 5, 2016 at 16:05
Do you want to return only those documents where the arrays are sorted? It is hard to understand the logic behind this. — Sede
– Sede, Commented Aug 5, 2016 at 19:44

Mr Tarsa · Accepted Answer · 2016-08-05 15:02:23Z

not at least one means noone

Query 1

For simplification, lets assign

A = "nids.0": { $ne: 0 }
B = "nids.1": { $ne: 10 }
C = { A, B }

then

{ "path" : { $elemMatch: C } }

will find documents where at least one element in path array satisfies condition C, while

{ "path" : { $not: { $elemMatch: C } } }

will find documents where there are no element in path array that satisfies condition C.

Document 1 and Document 2 don't have elements in their path arrays that satisfy condition C, thus the Query1 output contains both of them. If, f.e, you add to the path array of the Document 1

{ "nids": [ 1, 11, 110], "ctc" : 1 }

then Document 1 will not be in the output of Query 1 becase this added element satisfies C.

Query 2

For simplification, lets assign

C = { $nin: [0, 10] }

then

{ "path.nids" : { $not: { $elemMatch: C } } }

will find documents where there are no element in path.nids array that satisfies condition C.

Document 1 and Document 2 in their path.nids arrays have elements that satisfy condition C, thus the Query 2 output contains neither of them. If, f.e, you add to you collection document

{ "_id" : 6, "tpc" : 5, "path" : [ { "nids" : [ 0, 10 ], "ctc" : 1 } ] }

then it will be in the output of Query 2 because in path.nids array there are no elements that satisfy C.

Solution

In Query 1 replace

{ $elemMatch: { "nids.0": { $nin: [0] }, "nids.1": { $nin: [10] } } }

with

{ $elemMatch: { $or: [ { "nids.0": { $ne: 0 } }, { "nids.1": { $ne: 10 } } ] } }

This new Query will find documents where there are no element in path array that satisfies at least one of conditions A and B. So, it will find Document 1, but not Document 2 (where "nids" : [ 0, 5 ] does not satisfy condition B.

Note that { $ne: 10 } is equivalent to { $nin: [10] }.

Collectives™ on Stack Overflow

Find document with array that consists exclusively particular documents in MongoDB

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related