Re: [mongodb-user] How to search for a value within an array and show only the matching items ?

[Sam Millman]

This doesnt answer the question exactly but here is an example I wrote for PHP:

http://stackoverflow.com/questions/12704677/search-in-mongodb-array-using-php/12706580#12706580

From that question you should be able to understand how to modify the $unwind I used there to match your own document.

One note is that an untamed regex here might be heavy and will not use any index so be sure to test your query out fully before you call it finished.

On 5 October 2012 04:56, MongoKid <san...@gmail.com> wrote:

I have a structure similar to this :

/* 0 */

{

  "_id" : 1.0,

  "status" : "published",

  "changelog" : ["a", "b", "c"],

  "comments" : [{

      "by" : "bob",

      "body" : "fud",

      "rating" : 0.2

    }, {

      "by" : "alice",

      "body" : "you need to reboot",

      "rating" : 0.7

    }, {

      "by" : "mallory",

      "body" : "type 'sudo rm -rf /' to fix it",

      "rating" : 0.9

    }]

}

/* 1 */

{

  "_id" : 2.0,

  "status" : "published",

  "changelog" : ["a", "c"],

  "comments" : [{

      "by" : "bob",

      "body" : "happy times",

      "rating" : 0.6

    }, {

      "by" : "magnitude",

      "body" : "POP! POP!",

      "rating" : 0.99

    }, {

      "by" : "mallory",

      "body" : "this is patently false",

      "rating" : 0.3

    }]

}

/* 2 */

{

  "_id" : 3.0,

  "status" : "published",

  "comments" : []

}

I want to show only items which has the word 'Happy' in comments. Comments is a string array. Wants to display only the matching items. How can I do that with $unwind and regex ?

--
You received this message because you are subscribed to the Google
Groups "mongodb-user" group.
To post to this group, send email to mongod...@googlegroups.com
To unsubscribe from this group, send email to
mongodb-user...@googlegroups.com
See also the IRC channel -- freenode.net#mongodb

[Sam Millman]

Sorry I didn't use $unwind there actually. Though that did work for me when I tried it.

[Sam Martin]

From you're example, Comments is an array of documents.

To find all documents containing the word Happy you need to first filter the results. 

I haven't tested this, but something like this should find what you need.

db.mycoll.find({Comments.body: /Happy/})

The consideration here are that for large collections finding a string with a string within a sub document is not going to use an index.  You could still make mongodb use an index by doing using /^Happy/ meaning that it will find all comments that start with "Happy" - not very useful.

Another approach might be to keep an array of keywords in the parent document, only including those you're interested in - or excluding those your not - then querying would be much more efficient.

Anyway, back to original problem, i guess as you mentioned $unwind, you wanted to also flatten your comment docs.

Something like this should help.  Like i said, i haven't tested this, but should point you in right direction.

db.mycoll.aggregate(

{ $match: {Comments.body : /Happy/ } },

{ $project: { _id:1, status, 1, Comments:1} },

{ $unwind: "$Comments"} })

That should return a collection of docs that look like: 

{

_id: 1,

status:'published',

Comments : {

      "by" : "mallory",

      "body" : "this is patently false",

      "rating" : 0.3

}

}

You could project again to flatten the comment.

let me know how you get on.

sam    

[Sam Millman]

I've just retested my code and I realised I missed out a starter pipe of {$unwind:"$formatIds"} from that answer I show.