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
– JohnnyHK, Commented Oct 19, 2013 at 11:45
Even if it is some kind of pseudocode you should decide it you use and or &&. — zero323
– zero323, Commented Oct 19, 2013 at 12:06

zero323 · Accepted Answer · 2013-10-19 12:22:36Z

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, 2013 at 12:22

answered Oct 19, 2013 at 12:01

zero323

331k108 gold badges981 silver badges958 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Max Over a year ago

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)) )

zero323 Over a year ago

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: [{...}, {...}]}]}}

Max Over a year ago

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}] }

zero323 Over a year ago

You are clearly missing some operators but not, there is no difference.

Collectives™ on Stack Overflow

Mongodb query on nested array elements

1 Answer 1

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Linked

Related