Notes on Array arrays in Collection in MongoDB

I encountered an Array array query problem in Collection of mongoDB in the last two days. I was very clear after I couldn't understand it. I want to share it with you today.

The problem I encountered at that time was that I now have two documents, structured as follows:

{
"_id" : ObjectId("58e88fa90cf2b631bab2f0d8"),
"title" : "A",
"places" : [ 
    {
        "placeId" : "58d24465f323560340686c1a",
        "sourceType" : "articlePlace",
        "status":1
    }, 
    {
        "placeId" : "58de05f10cf2c09e03c3542b",
        "sourceType" : "travelPlace",
        "status":1
    }
],
"status" : 1
}
{
"_id" : ObjectId("58e89caaf323560340686c31"),
"title" : "B",
"places" : [ 
    {
        "placeId" : "58d24465f323560340686c1a",
        "sourceType" : "articlePlace",
        "status":1
    }, 
    {
        "placeId" : "58de05f10cf2c09e03c3542b",
        "sourceType" : "articlePlace",
        "status":1
    }
],
"status" : 1
}

Document A and Document B have an array of places, where placeId is 58 de05f10 CF2 c09e03c3542b and both documents have different sourceTypes. Now I need to filter out the placeId to 58 de05f10 CF2 c09e03c3542b and the corresponding sourceType to document A of travelPlace.

When I encounter this problem, first of all, my query statement looks like this:

db.getCollection('test').find({"places.placeId":"58de05f10cf2c09e03c3542b","places.sourceType":"travelPlace"})  

The results are as follows:

{
"_id" : ObjectId("58e88fa90cf2b631bab2f0d8"),
"title" : "A",
"places" : [ 
    {
        "placeId" : "58d24465f323560340686c1a",
        "sourceType" : "articlePlace",
        "status":1
    }, 
    {
        "placeId" : "58de05f10cf2c09e03c3542b",
        "sourceType" : "travelPlace",
        "status":1
    }
],
"status" : 1
}

It should look correct, but when I change the query statement:

db.getCollection('test').find({"places.placeId":"58de05f10cf2c09e03c3542b","places.sourceType":"articlePlace"}) 

The result is:

{
"_id" : ObjectId("58e88fa90cf2b631bab2f0d8"),
"title" : "A",
"places" : [ 
    {
        "placeId" : "58d24465f323560340686c1a",
        "sourceType" : "articlePlace",
        "status":1
    }, 
    {
        "placeId" : "58de05f10cf2c09e03c3542b",
        "sourceType" : "travelPlace",
        "status":1
    }
],
"status" : 1
}

{
"_id" : ObjectId("58e89caaf323560340686c31"),
"title" : "B",
"places" : [ 
    {
        "placeId" : "58d24465f323560340686c1a",
        "sourceType" : "articlePlace",
        "status":1
    }, 
    {
        "placeId" : "58de05f10cf2c09e03c3542b",
        "sourceType" : "articlePlace",
        "status":1
    }
],
"status" : 1
}

Both documents have been queried out!what? Why hit two records?

Let's analyze the query statement:

db.getCollection('test').find({"places.placeId":"58de05f10cf2c09e03c3542b","places.sourceType":"articlePlace"}) 

This statement has two conditions:

• Query out all places.placeId documents for 58 de05f10cf2c09e03c3542b;
• Query out all documents with places.sourceType as articlePlace

When these two conditions are combined, they are executed as follows:

• First find all places.placeId equal to 58de05f10cf2c09e03c3542b documents, hit Document A and Document B, because there is a placeId of 58de05f10cf2c09e03c3542b in the Places subset of Document A, and the sourceType is a travelPlace document, so hit Document A, there is also a placeId of 58de05f10cf2c09e03c3542 in Document BB is a subset of documents;
• Then find out all the documents where places.sourceType equals articlePlace. The hit result is also document A and document B. Its logic is that document A has a placeId of 58 d24465 f3235600686c1a, sourceType is a subset of articlePlace, document B has two sourceTypes of articlePlace and different document subsets of placeId.
• Combine the two results to get the intersection;

We can see that the granularity of query statements for places array documents is based on the entire document, the intersection of two constraints, not the intersection of places subdocuments for each document.

Here's an example: I have a budget of one hundred dollars. I go to the convenience store to buy one hundred items A and A. Then I go home. The second time I go to the convenience store to buy one hundred items B and B. Then I go home again. When I get home, I put the two purchases together and find the total value of the two purchases is two hundred yuan.Overrun!My original plan was to pick one of the two AB products and spend only one hundred dollars.As a result, I bought it twice, and then I bought a hundred each time. The result is wrong.

Now that we have found a problem, the next thing we need to solve is how to complete the whole query constraint on the list of subdocuments in one constraint. I will change the original query condition to the following form:

db.getCollection('test').find({"places":{"placeId":"58de05f10cf2c09e03c3542b","sourceType":"articlePlace"}})

Now the conditions are: places is queried once, the constraints are placeId equal to 58de05f10cf2c09e03c3542b, sourceType equal to articlePlace;

No results were returned after execution!Why?Check the official documentation for a paragraph about "Query a Array":

Match an Array

To specify equality condition on an array, use the query document { <field>: <value> } where <value> is the exact array to match, including the order of the elements. 

The original operation through this {:} requires that the subsequent value exactly match the array subset, but for the subset in Array, there may be many other things in the actual project, which may be different. If you want to query accurately, you will only get an accurate value, which does not meet our needs.We need to do multiple qualifying queries on a subset of arrays, but we need to ignore those unnecessary elements and finally have no choice but to sacrifice the killer mace - see the official API.

Query for an Array Element that Meets Multiple Criteria

Use $elemMatch operator to specify multiple criteria on the elements of an array such that at least one array element satisfies all the specified criteria.

We used the $elemMacth query criteria when we needed to have multiple matches for a subset of array s, so our Query statement eventually became like this:

db.getCollection('test').find({"places":{$elemMatch:{"placeId":"58de05f10cf2c09e03c3542b","sourceType":"articlePlace"}}})

Post-execution results:

{
"_id" : ObjectId("58e89caaf323560340686c31"),
"title" : "B",
"places" : [ 
    {
        "placeId" : "58d24465f323560340686c1a",
        "sourceType" : "articlePlace",
        "status" : 1
    }, 
    {
        "placeId" : "58de05f10cf2c09e03c3542b",
        "sourceType" : "articlePlace",
        "status" : 1
    }
],
"status" : 1
}

get! Let's change sourceType again:

db.getCollection('test').find({"places":{$elemMatch:{"placeId":"58de05f10cf2c09e03c3542b","sourceType":"travelPlace"}}})

Execution results:

{
"_id" : ObjectId("58e88fa90cf2b631bab2f0d8"),
"title" : "A",
"places" : [ 
    {
        "placeId" : "58d24465f323560340686c1a",
        "sourceType" : "articlePlace",
        "status" : 1
    }, 
    {
        "placeId" : "58de05f10cf2c09e03c3542b",
        "sourceType" : "travelPlace",
        "status" : 1
    }
],
"status" : 1
}

If you need to do a holistic composite query on the Array subset of Collection, you need to use $elemMatch, and if you need a precise query, use the {:} format.

Hope it can help you, thank you~