MongoDB Query匹配单个条目和数组元素

I have a problem with MongoDB QueryBuilder.

我有MongoDB QueryBuilder的问题。

Assume I have a number of documents, that can contain one or more users:

假设我有许多文档,可以包含一个或多个用户:

{
  "_id": "document1",
  "data": {
    "user": {
      "credentials": {
        "name": "John",
        "lastname": "Watson",
        "middle": "Hemish"
      }
    }
  }
}


{
  "_id": "document2",
  "data": {
    "user": [
      {
        "credentials": {
          "name": "John",
          "lastname": "Nicholson",
          "middle": "Joseph"
        }
      },
      {
        "credentials": {
          "name": "Mary",
          "lastname": "Watson",
          "middle": ""
        }
      }
    ]
  }
}


{
  "_id": "document3",
  "data": {
    "user": [
      {
        "credentials": {
          "name": "John",
          "lastname": "Watson",
          "middle": "Hemish"
        }
      },
      {
        "credentials": {
          "name": "John",
          "lastname": "Nicholson",
          "middle": "Joseph"
        }
      },
      {
        "credentials": {
          "name": "Mary",
          "lastname": "Watson",
          "middle": ""
        }
      }
    ]
  }
}

What I am trying to do is the query, that will return only those documents containing John Watson as a user.

我想要做的是查询,它将仅返回包含John Watson作为用户的那些文档。

Here what I got so far:

这是我到目前为止所得到的:

QueryBuilder qb = QueryBuilder.start("credentials.lastname").is("Watson").and("credentials.name").is("John");
DBObject query = QueryBuilder.start("data.user").elemMatch(qb.get()).get();

this query will return only document3: there is no array in document1 and no match in document2 (but I would like it to return document1 and document3)

此查询将仅返回document3:document1中没有数组,document2中没有匹配项(但我希望它返回document1和document3)

DBObject query = QueryBuilder.start("data.user.credentials.lastname").is("Watson").and("data.user.credentials.name").is("John").get();

this one will return all three documents: document1 and document3 are desired match, but the query will match as well document2, for it has Watson and John in query fields in the array, no matter that they are separate entries.

这个将返回所有三个文档:document1和document3是匹配的,但查询也将匹配document2,因为它在数组的查询字段中有Watson和John,无论它们是单独的条目。

Is there any way to make a right query that will return document1 and document3 for John Watson?

有没有办法做出正确的查询,将返回John Watson的document1和document3?

I am trying to do it in Java, but any other example would be fine.

我试图用Java做,但任何其他例子都没问题。

Right now I use a workaround combining results from both queries: first I get limit(100) results from the query with elementMatch(), then, if there are less than 100 results, I do the second query and filter all wrong matches. But I hope there is a better and more effective way to get those results.

现在我使用一种解决方法来合并两个查询的结果:首先我使用elementMatch()获得限制(100)结果,然后,如果结果少于100,我会执行第二次查询并过滤所有错误匹配。但我希望有更好,更有效的方法来获得这些结果。

1 个解决方案

#1

I could give you at best like the following where user would be in an array as unwind value of the key data. I think a little bit more effort would lead you to the exact format as you want.

我可以给你最好像下面这样的用户将在数组中作为关键数据的展开值。我认为一点点努力会让你得到你想要的确切格式。

I am sharing it as I think it should serve the purpose or anyhow it should help you.

我分享它,因为我认为它应该服务于目的或无论如何它应该帮助你。

The aggregation query:

聚合查询:

db.tuttut.aggregate([
    {$unwind:"$data.user"},
    { $project: { 
        _id:1, 
        data:1, 
        temp: {name:"$data.user.credentials.name",
               lastname:"$data.user.credentials.lastname"}  
        } }  ,
    { $group:{ 
        _id:"$_id" , 
        data: {$addToSet: "$data"} , 
        temp:{ $addToSet: "$temp"  } }  }, 
    { $match:{ temp:{name:"John",lastname:"Watson"}  }  }  ,
    {$project:{_id:1, data:1}}
]).pretty()

Returned Result:

{
        "_id" : "document1",
        "data" : [
                {
                        "user" : {
                                "credentials" : {
                                        "name" : "John",
                                        "lastname" : "Watson",
                                        "middle" : "Hemish"
                                }
                        }
                }
        ]
}
{
        "_id" : "document3",
        "data" : [
                {
                        "user" : {
                                "credentials" : {
                                        "name" : "John",
                                        "lastname" : "Watson",
                                        "middle" : "Hemish"
                                }
                        }
                },
                {
                        "user" : {
                                "credentials" : {
                                        "name" : "Mary",
                                        "lastname" : "Watson",
                                        "middle" : ""
                                }
                        }
                },
                {
                        "user" : {
                                "credentials" : {
                                        "name" : "John",
                                        "lastname" : "Nicholson",
                                        "middle" : "Joseph"
                                }
                        }
                }
        ]
}

#1