0

Let say I have this kind of document structured, the attributes field will be the embedded document and I've already indexed the attributes.key and attributes.value

1-------------------------------------------------------------------------------------
{ 
  "_id" : ObjectId( "5191d8e5d00560402e000001" ),
  "attributes" : [ 
  { "key" : "pobox","value" : "QaKUWo" }, 
  { "key" : "city", "value" : "CBDRip" }, 
  { "key" : "address","value" : "zmycAa" } ],
  "email" : "FWAUdl_2@email.com",
  "firstname" : "FWAUdl_2" 
}
2-------------------------------------------------------------------------------------
{ 
  "_id" : ObjectId( "5191d8e7d00560402e000055" ),
  "attributes" : [ 
    { "key" : "pobox", "value" : "sNFriy" }, 
    { "key" : "city", "value" : "JPdVrI" }, 
    { "key" : "address", "value" : "phOluW" } ],
  "email" : "hqYNWH_86@email.com",
  "firstname" : "hqYNWH_86" 
}

My problem is how to get exact document when querying based only on the attributes field,

db.app.find({ attributes.key:address , attributes.value:/.*uw.*/i })

The query result is not as I expected, it should result only the 2nd document only without the 1st document. I know that I put regex on the attributes.value, I was expecting that it only check for attributes.key that have address value.

And what if I want to filter another key, such like,

db.app.find({ attributes.key:address , attributes.value:/.*uw.*/i , attributes.key:city , attributes.value:/.*ri.*/i })

Any opinion will be helpful guys. Thx.

Khalid Adisendjaja
  • 1
  • 6

2 Answers2

1

I guess you need $elemMatch ( http://docs.mongodb.org/manual/reference/operator/elemMatch/ )

db.test123.find({ attributes : { $elemMatch : { 'key':"address" , 'value':/.*uw.*/i } } }).pretty()
{
    "_id" : ObjectId("5191d8e7d00560402e000055"),
    "attributes" : [
        {
            "key" : "pobox",
            "value" : "sNFriy"
        },
        {
            "key" : "city",
            "value" : "JPdVrI"
        },
        {
            "key" : "address",
            "value" : "phOluW"
        }
    ],
    "email" : "hqYNWH_86@email.com",
    "firstname" : "hqYNWH_86"
}
Abhishek Kumar
  • 3,328
  • 2
  • 18
  • 31
  • Is the $elemMatch operator using index? I have some way to work around using the $where operator, checking through all the item on the attribute list, which come like this, db.mycollection.find({ $where : function() { for(var idx in this.attributes){if(this.attributes[idx].key == 'address' && this.attributes[idx].value.match(/.*xl.*/i)){return true;}}); But this method I think will not gonna use the index. – Khalid Adisendjaja May 15 '13 at 04:22
  • TO make the query use the index, index on : db.testing.ensureIndex({'attributes.key':1,'attributes.value':1}). Also use the old style of querying your collection i.e. db.testing.find({ 'attributes.key':"address" , 'attributes.value' : "phOluW" } ).explain() I have mostly heard that javascript queries are generally slow, not sure now. – Abhishek Kumar May 15 '13 at 05:33
  • @KhalidAdisendjaja : Just a small note, though from my earlier comment I am asking you to use old style of querying. But this won't cause the duplicate issue for which you actually posted the problem, because it tries to use the index this time and make the correct match. Following regex will also work. db.testing.find({ 'attributes.key':"address" , 'attributes.value' : /.*uw.*/i } ).explain() – Abhishek Kumar May 15 '13 at 05:37
  • @KhalidAdisendjaja : sorry about the indexing part, db.testing.find({ 'attributes.key':"address" , 'attributes.value' : /.*uw.*/i } ) is using the index, but then it is scanning the documents at the end and returning the unwanted results – Abhishek Kumar May 15 '13 at 07:02
  • No can't use the regular find query, coz it will search another value pair which key is not "address", my option is to use $elemMatch or $where operator, just curious about the performance if the $elemMatch query will used the index (attributes.key,attributes.value) on the process. – Khalid Adisendjaja May 15 '13 at 07:59
  • Hmm or maybe, I'm goin to change the embedded documents into a HashBag, indexing manually all the key `{ "_id" : ObjectId( "51921b32d005607d39000002" ), "name" : "Tljlpa_1", "attributes" : { "address" : { "value" : "NUznXY", "label" : "address" }, "company" : { "value" : "zpMPgJ", "label" : "company" }, "city" : { "value" : "KMJnQv", "label" : "city" } } }` – Khalid Adisendjaja May 15 '13 at 08:04
  • If you don't have regex match on the 'value' then the index on 'attributes' will work perfectly fine i.e. db.testing.find({ 'attributes' : {'key' : 'address' , 'value' : 'phOluW' } }) otherwise $elemMatch is not using the index. Also, if you don't have regex queries then you can optimize your document storage. { "_id": ObjectId("5191d8e5d00560402e000001"), "attributes": [{ "pobox" : "QaKUWo" }, { "city": "CBDRip" } ], "email": "FWAUdl_2@email.com", "firstname": "FWAUdl_2" } – Abhishek Kumar May 15 '13 at 08:23
0

Just investigated a little and figured out following. The following uses the index mentioned below. You can do a explain() on the find() to check more index usage details

db.testing.getIndexKeys()
[ { "_id" : 1 }, { "attributes.key" : 1, "attributes.value" : 1 } ]

test:Mongo > db.testing.find({$and : [ { attributes : {$elemMatch : {key : 'address', value : /.*uw.*/i }} }, { attributes : {$elemMatch : {key : 'city', value : /.*ri.*/i }} }] }).pretty()
{
    "_id" : ObjectId("5191d8e7d00560402e000055"),
    "attributes" : [
        {
            "key" : "pobox",
            "value" : "sNFriy"
        },
        {
            "key" : "city",
            "value" : "JPdVrI"
        },
        {
            "key" : "address",
            "value" : "phOluW"
        }
    ],
    "email" : "hqYNWH_86@email.com",
    "firstname" : "hqYNWH_86"
}
Abhishek Kumar
  • 3,328
  • 2
  • 18
  • 31