Search code examples
javajsonmongodbmorphia

Java morphia - query by values in a list field

Not sure if this is possible, but here goes.

I have a MongoDB (using morphia to access it) with a table like the following (simplified for this example, but the concept is the same):

{ Name : "Robert", NickNames : [ "Bob", "Twinkletoes" ] }
{ Name : "Trevor", NickNames : [ "Bob", "Mad man", "Tweedledee" ] }
{ Name : "Samuel", NickNames : [ "Sam" ] }
{ Name : "Patrick", NickNames : [ "Bob", "Mad man" ] }
{ Name : "Mark", NickNames : [ "Adam", "Bob", "Georg" ] }

And for the purpose of this example let's say the nicknames are always in alphabetical order and the position where the nickname lies is relevent.

So, as we can see every person record can have a different number of nicknames in the list. How can I perform the following searches:

  • Give me all people with the first nickname "Bob" and the second nickname "Mad man" (i.e. Person.Nicknames[0] = "Bob" && Person.Nicknames[1] = "Mad man")

    Should return 2 records - Trevor and Patrick.

  • Give me all people with the first nickname "Bob" (i.e. Person.Nicknames[0] = "Bob")

    Should return 3 records - Robert, Trevor and Patrick.

Note that Mark wasn't returned because he his nickname of "Bob" wasn't in the first index.

Is there any way to do this?

Solution

  • Once elements are inserted into an array, their order will be preserved.

    The queries should work as you expect them to: (_id omitted for brevity)

    > db.people.find({"NickNames.0":"Bob", "NickNames.1":"Mad man"}, {_id:0})
{ "Name" : "Trevor", "NickNames" : [ "Bob", "Mad man", "Tweedledee" ] }
{ "Name" : "Patrick", "NickNames" : [ "Bob", "Mad man" ] }
> db.people.find({"NickNames.0":"Bob"}, {_id:0})
{ "Name" : "Robert", "NickNames" : [ "Bob", "Twinkletoes" ] }
{ "Name" : "Trevor", "NickNames" : [ "Bob", "Mad man", "Tweedledee" ] }
{ "Name" : "Patrick", "NickNames" : [ "Bob", "Mad man" ] }
>

    There is a brief note on this in the "Array Element by Position" section of the "Dot Notation (Reaching into Objects)" documentation: http://www.mongodb.org/display/DOCS/Dot+Notation+%28Reaching+into+Objects%29#DotNotation%28ReachingintoObjects%29-ArrayElementbyPosition

    The above was done in the JS shell. I am not familiar with Morphia, so you will have to translate the queries into the correct Morphia syntax yourself. Hope this helps!