Searching a set of documents nested in another document

Question

I have an interesting problem at work. We have a document structure like this:

{
"q" : "c1179beb-2db4-4052-b10d-f0c1332436f3",
"ssm" : {
    "0168edb0-c25d-42ba-ba6d-918c4f61f6e1" : {
        "xas" : [],
        "dob" : ISODate("2001-01-01T00:00:00.000Z"),
        "we" : "bow-bq",
        "gndr" : "M",
        "lcd" : ISODate("2014-02-21T09:00:04.575Z"),
        "re" : "tyjhgj",
        "s" : "0168edb0-c25d-42ba-ba6d-918c4f61f6e1",
        "scsv" : "testdata",
        "sg" : {
            "grd" : "FOUR",
            "gdn" : "4"
        },
        "sno" : "100000002",
        "sss" : "Ha",
        "stst" : {
            "tk" : "226",
            "tn" : "That"
        },
        "tsd" : ISODate("2014-02-21T08:35:51.075Z")
    },
    "016e48fa-5c24-4fd5-b078-5333991de013" : {
        "xas" : [],
        "dob" : ISODate("2001-01-01T00:00:00.000Z"),
        "we" : "dsgsdf",
        "gndr" : "M",
        "lcd" : ISODate("2014-02-21T09:04:11.995Z"),
        "lia" : 53,
        "re" : "ghjghjgh",
        "s" : "016e48fa-5c24-4fd5-b078-5333991de013",
        "scsv" : "testdata",
        "sg" : {
            "grd" : "FOUR",
            "gdn" : "4"
        },
        "sno" : "100000029",
        "sss" : "Ha",
        "stst" : {
            "tk" : "86",
            "tn" : "That"
        },
        "tsd" : ISODate("2014-02-21T08:50:55.821Z")
    },
    "076fc7ab-264b-4814-9f3a-db9f8222d02f" : {
        "xas" : [],
        "dob" : ISODate("2001-01-01T00:00:00.000Z"),
        "we" : "hjhyyio",
        "gndr" : "M",
        "lcd" : ISODate("2014-02-21T09:04:58.299Z"),
        "lia" : 53,
        "re" : "qsdrfg",
        "s" : "076fc7ab-264b-4814-9f3a-db9f8222d02f",
        "scsv" : "testdata",
        "sg" : {
            "grd" : "FOUR",
            "gdn" : "4"
        },
        "sno" : "100000003",
        "sss" : "HA",
        "stst" : {
            "tk" : "161",
            "tn" : "That"
        },
        "tsd" : ISODate("2014-02-21T08:52:04.471Z")
    },
"try" : "34534",
"jyu" : ISODate("2014-03-01T07:59:59.000Z"),
"poij" : ISODate("2014-01-08T08:00:00.000Z")

}
}

Obviously this is data that has been edited to hide the source but the structure is the same.

I am trying to write a query that will search on the "s" field of the sub-documents contained in the sub-document "ssm".

I have tried using $elementmatch as well as using standard dot notation. The issue I am running into is that the "ssm" field is more of a sub-collection than a sub-document. I'm a little lost and am hoping for a few suggestions.

Reading the Mongo documentation I can't seem to find any way to query sub-collections. Google shows me plenty of ways to query an ARRAY of sud-documents but that is not what I'm dealing with here.

Please Help!

score 0 · Accepted Answer · edited May 23 '17 at 10:31

As you are aware, your listed items are sub-documents and not members of an array. As such you have no way of referencing the underlying values without knowing the composition of the keys in the levels of nesting.

I reference this answer of mine a bit, all on the same topic, so probably worth a read. But the data structure here is greatly flawed if you want to do any kind of meaningful query analysis on it. As such it should be changed and the other answer and your own findings should be compelling reasons to do so.

There is no simple solution to dealing with a structure like this, this question and answer may give some insight into how to "discover" the key names in order to feed to query information. But that will be a laborious process.

A good strength of MongoDB is that it allows you to change things with a lot more flexibility than a relational setup. The flexibility in document design also gives you "enough rope to hang yourself", and re-design is often a necessity over the evolution of your development.

Get the structure changed to something that works.

Thanks! You confirmed my suspicions. I agree that the structure is fundamentally wrong and I am not quite sure why it was designed that way. Unfortunately that decision was way above my pay grade. I will continue to try to point out the flaws in the design and hopefully someone will listen. With that said, I have not tried using the unwind function as you do in your other answer. I will give that a try and see if I can cobble something together. Eventually I will most likely fall back on my tried and true python/pymongo combo to unscrew the structure of the collections. Thanks Again! — nrmjba, Feb 22 '14 at 02:34

score 0 · Answer 2 · answered Feb 22 '14 at 01:53

Instead of this:

{
"q" : "c1179beb-2db4-4052-b10d-f0c1332436f3",
"ssm" : {
    "0168edb0-c25d-42ba-ba6d-918c4f61f6e1" : {
        "dob" : ISODate("2001-01-01T00:00:00.000Z"),
        },
    },
    "016e48fa-5c24-4fd5-b078-5333991de013" : {
        "dob" : ISODate("2001-01-01T00:00:00.000Z"),
    }
}

Try this:

{
"q" : "c1179beb-2db4-4052-b10d-f0c1332436f3",
"ssm" : [
          {
           "name": "0168edb0-c25d-42ba-ba6d-918c4f61f6e1",
           "dob" : ISODate("2001-01-01T00:00:00.000Z"),
          },
          {
           "name": "016e48fa-5c24-4fd5-b078-5333991de013",
           "dob" : ISODate("2001-01-01T00:00:00.000Z"),
          }
 ],
 "foo": "bar"
}

That way, you can search for documents where someone has a DOB via find(ssm.dob: date). Once you find your document, you'll still have to scan the sub-documents to see which one matched.

If you don't like that, you'll need to create each someone as a document in a different collection.

I agree that the structure is wrong. if I had it my way i would change it. Unfortunately that is not my decision. Thank you for the example of what it should look like though! — nrmjba, Feb 22 '14 at 02:38

Searching a set of documents nested in another document

2 Answers2