Finds all the documents were the an array field contains a document that match some conditions

Question

I've a MongoDB collection that store all the user data.

A document of my collection has the following JSON form:

{
    "_id" : ObjectId("542e67e07f724fc2af28ba75"),
    "id" : "",
    "email" : "luigi@gmail.com",
    "tags" : [
        {
            "tag" : "Paper Goods:Liners - Baking Cups",
            "weight" : 2,
            "lastInsert" : 1412327492874
        },
        {
            "tag" : "Vegetable:Carrots - Jumbo",
            "weight" : 4,
            "lastInsert" : 1412597883569
        },
        {
            "tag" : "Paper Goods:Lialberto- Baking Cups",
            "weight" : 1,
            "lastInsert" : 1412327548205
        },
        {
            "tag" : "Fish:Swordfish Loin Portions",
            "weight" : 3,
            "lastInsert" : 1412597939124
        },
        {
            "tag" : "Vegetable:Carrots - alberto@gmail.com",
            "weight" : 2,
            "lastInsert" : 1412597939124
        }
    ]
}

The tag field is in the form "category:name product" and the "tags" field contains all the product bought by an user.

I'm writing a Scala application, and I'm using the reactivemongo driver. Now I'm writing a method that given a category and a product search all the user that have bought at least a product of the given category, but had not already bought no one product equals to the given.

My code now is like the following:

def findUsers(input: FindSuggestion): Future[Option[List[User]]] = {
      val category = input.category //a string
      val product = input.product  //a string, in the form category:productName
      val query = Json.obj(//create the query object)
      Users.find(query).toList.flatMap(users =>
        if(users.size > 0)
          Future{Some(users)}
        else
          Future{None}
          )
    }

To be more specific I search all the document where the tags field contain a document where the tag field starts with category, but the tags field doesn't contain any document where tag == product.

How can i make that in mongodb??

sh1ng · Accepted Answer · 2014-10-21T07:20:31.443

1

For efficient querying you should change structure of documents, for example like

{
    "_id" : ObjectId("542e67e07f724fc2af28ba75"),
    "id" : "",
    "email" : "luigi@gmail.com",
    "tags" : [
        {
            "category": "Fish",
            "product": "Swordfish Loin Portions"
            "weight" : 2,
            "lastInsert" : 1412327492874
        },
        {
            "category": "Vegetable",
            "product": "Carrots - Jumbo",
            "weight" : 4,
            "lastInsert" : 1412597883569
        }
    ]
}

And create index over category and product

Query will be

$and:[{"tags.category":"requestedCategory"},{$ne:{"tags.product":"requestedProduct"}}]

And if you decide don't modify the structure, the query is

$and:[{"tags.tag":$regex: 'requestedCategory:.*'},{$ne:{"tags.tag":"requestedCategory:requestedProduct"}}]

Update. Adding an index

db.collection.ensureIndex( { "tags.tag": 1 } )

edited Oct 21 '14 at 07:20

answered Oct 20 '14 at 09:50

sh1ng

2,808
4
24
38

Hi, thanks for your answer. I can't change the structure of my documents. I would to answered you how to specify indexes on mongodb in this case?? I haven't already used index in mongodb. – alberto adami Oct 20 '14 at 12:20
2

The syntax for the queries is not right. The queries should be `{ "tags.category" : "requestedCategory", "tags.product" : { "$ne" : "requestedProduct" } }` and `{ "tags.tag" : { "$regex" : "Paper Goods:.*" }, "tags.tag" : { "$ne" : "Paper Goods:Lialberto- Dunking Cups" } }`, respectively. Absolutely change your document structure as advised in the answer. Why can't you change it? all you need to do is add fields that will make searching easier and more efficient. – wdberkeley Oct 20 '14 at 15:06
Thanks for the comment, I've made a mistake in the field name. But I think you query give same result as mine. – sh1ng Oct 20 '14 at 17:30
I can't change the structure because there are many data in the collection @wdberkeley. I didn't create this structure, I'm working on a project that had this structure. – alberto adami Oct 21 '14 at 12:46

Finds all the documents were the an array field contains a document that match some conditions

1 Answers1