CODEWITHSUNDEEP
mongodb
Max
Max

Reputation: 121

Mongodb query on nested array elements

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

Upvotes: 0

Views: 10100

Answers (1)

zero323
zero323

Reputation: 330393

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.

Upvotes: 3

Related Questions