Mongodb query on nested array elements

Question

for my new project we started to use mongodb as a document repository. It works great for our requirements but now we need to implement a more advanced query on sub-sub-array element.

This is an example of our documents:

        {
            "Asset" : {
                "Metadata" : {
                    "Titolo" : {
                        "Value" : "Titolo 50271235"
                    },
                    "Platforms" : {
                        "Platform" : [ 
                            {
                                "@name" : "MY_PLATFORM_1",
                                "PublishingRanges" : {
                                    "PublishingRange" : [
                                        {
                                            "@startdate" : ISODate("2013-09-05T00:00:00Z"),
                                            "@enddate" : ISODate("2013-11-04T23:59:00Z")
                                        },
                                        {
                                            "@startdate" : ISODate("2013-10-05T00:00:00Z"),
                                            "@enddate" : ISODate("2014-11-04T23:59:00Z")
                                        }
                                    ]
                                }
                            }, 
                            {
                                "@name" : "MY_PLATFORM_2",

                                "PublishingRanges" : {
                                    "PublishingRange" : [
                                        {
                                            "@startdate" : ISODate("2013-09-05T00:00:00Z"),
                                            "@enddate" : ISODate("2013-11-04T23:59:00Z")
                                        },
                                        {
                                            "@startdate" : ISODate("2013-09-05T00:00:00Z"),
                                            "@enddate" : ISODate("2013-11-04T23:59:00Z")
                                        }
                                    ]
                                }
                            }, 
                            {
                                "@name" : "MY_PLATFORM_3",
                                "AmbienteDiPubblicazione" : {
                                    "#" : "Produzione"
                                },
                                "PublishingRanges" : {
                                    "PublishingRange" : [
                                        {
                                            "@startdate" : ISODate("2013-09-05T00:00:00Z"),
                                            "@enddate" : ISODate("2013-11-04T23:59:00Z")
                                        },
                                        {
                                            "@startdate" : ISODate("2013-09-05T00:00:00Z"),
                                            "@enddate" : ISODate("2013-11-04T23:59:00Z")
                                        }
                                    ]
                                }
                            }, 
                            {
                                "@name" : "MY_PLATFORM_4",
                                 "PublishingRanges" : {
                                    "PublishingRange" : [
                                        {
                                            "@startdate" : ISODate("2013-09-05T00:00:00Z"),
                                            "@enddate" : ISODate("2013-11-04T23:59:00Z")
                                        },
                                        {
                                            "@startdate" : ISODate("2013-09-05T00:00:00Z"),
                                            "@enddate" : ISODate("2013-11-04T23:59:00Z")
                                        }
                                    ]
                                }
                            }
                        ]
                        }
                    }
                }
            }
        }

As you can see, we have an array of "Platform" which have inside an array of "PublishingRange". What we need is to find all the documents which the element "Platform" satisfy these constraints:

Platform.@name == VAR_PLATFORM
and(
    (PublishingRange.@startdate > VAR_START && PublishingRange.@startdate < V_END)
)

Of course, the constraints on dates must be sastisfied by the "Platform" with name== VAR_PLATFORM, and not on a different "Platform"

I've tried using an $elemMatch based query but without success.

Thank you for any suggestion.

Max

Could you edit your question to show what you tried with `$elemMatch` and what didn't work about it? — JohnnyHK, Oct 19 '13 at 11:45
Even if it is some kind of pseudocode you should decide it you use `and` or `&&`. — zero323, Oct 19 '13 at 12:06

zero323 · Accepted Answer · 2013-10-19T12:22:36.340

3

Since there is no way to tell what driver you use here is a shell solution:

db.foo.find({
    "Asset.Metadata.Platforms.Platform": {
        $elemMatch: {
            "@name": VAR_PLATFORM,
            "PublishingRanges.PublishingRange": {
                $elemMatch: {
                    "@startdate": {$gt: VAR_START},
                    "@enddate": {$lt: VAR_END}
                }
            }
        }
    }  
})

By the way, you could simplify document structure by omitting Platform and PublishingRange and assigning arrays to Platforms an PublishingRanges respectively.

edited Oct 19 '13 at 12:22

answered Oct 19 '13 at 12:01

zero323

322,348
103
959
935

Thank you very much zero323. It seems to work, I just fixed the filter on dates. What I don't understand is the difference between $elemMatch: { "@startdate": {$gt: VAR_START}, "@enddate": {$lt: VAR_END} } VS $elemMatch: { $and:[ {"@startdate": {$gt: VAR_START}}, {"@enddate": {$lt: VAR_END}} ] } I post this question, because I need to add others criterias on dates: Platform.@name == VAR_PLAT AND( ((PublishingRange.@startdate > V_START AND PublishingRange.@startdate < V_END)) OR ((PublishingRange.@enddate > V_START AND PublishingRange.@enddate < V_END)) ) – Max Oct 22 '13 at 09:01
There is no difference. In MongoDB `{foo: 1, bar: 1}` is equivalent to `{$and: [{foo: 1}, {bar: 1}]}`. So you can use `{$elemMatch: {$or: [{...}, {...}]}}` as well as `{$elemMatch: {$or: [{$and:[{...}, {...}]}, {$and: [{...}, {...}]}]}}` – zero323 Oct 22 '13 at 12:22
Many thanks again. There is difference in query results if an array element contains also other optional fields?? For example: PublishingRanges : {PublishingRange : [{startdate: x, enddate: y, optionalField1: z}, {startdate: xx, enddate: yy, optionalField2: zz}, {startdate: xxx, enddate: yyy}] } – Max Oct 22 '13 at 14:38
You are clearly missing some operators but not, there is no difference. – zero323 Oct 22 '13 at 14:45

Mongodb query on nested array elements

1 Answers1

Linked