slice to find out the max element in deep nested array in mongodb

140 views

Skip to first unread message

senthil kumar

unread,

Nov 20, 2017, 9:58:42 AM11/20/17

to mongodb-user

I am using mongodb 3.4.2 and want to find out the maximum element in deep nested array in mongodb. I tried using $slice operator to find maximum element in array but was a failure, Please help

Sample Document record :

        {
    "_id" : ObjectId("562e7c594c12942f08fe4192"),
    "inventory" : {
        "id" : "I0001",
        "name" : "Fish"
    },
    "shapes" : [
        {
            "shape" : "square",
            "color" : "blue",
            "fileArray" : [
                {
                    "fileid" : "ID001",
                    "filename" : "Abc.jpg",
                    "filepath" : "d:/temp/",
                    "version" : "1.0"
                },
                {
                    "fileid" : "ID002",
                    "filename" : "Abc.jpg",
                    "filepath" : "d:/temp/",
                    "version" : "2.0"
                },
                {
                    "fileid" : "ID003",
                    "filename" : "Abc.jpg",
                    "filepath" : "d:/temp/",
                    "version" : "3.0"
                },
                {
                    "fileid" : "ID002",
                    "filename" : "Cde.jpg",
                    "filepath" : "d:/temp2/",
                    "version" : "1.0"
                }
            ]
        },
        {
            "shape" : "circle",
            "color" : "red",
            "fileArray" : [
                {
                    "fileid" : "ID003",
                    "filename" : "Abc.jpg",
                    "filepath" : "d:/temp/",
                    "version" : "1.0"
                },
                {
                    "fileid" : "ID002",
                    "filename" : "Abc.jpg",
                    "filepath" : "d:/temp/",
                    "version" : "2.0"
                },
                {
                    "fileid" : "ID003",
                    "filename" : "Abc.jpg",
                    "filepath" : "d:/temp/",
                    "version" : "3.0"
                },
                {
                    "fileid" : "ID004",
                    "filename" : "Cde.jpg",
                    "filepath" : "d:/temp2/",
                    "version" : "1.0"
                }
            ]
        }
    ]
    }

Query to get the max version element in the deeply nested array



      db.runCommand(

         {
          aggregate : 'dummies',
          pipeline : [
           {$match: { $and : [{'inventory.name':'Fish'}
           ,
           { shapes : {
             $elemMatch :{$and : [
                 {'color':'blue'}

                  ]
                 }

         }}]
         }}
      ,{ '$addFields': {
    'shapes': {
      '$filter': {
        'input': {
          '$map': {
            'input': '$shapes',
            'as': 'sa',
            'in': {
              'color': '$$sa.color',
              'fileArray': {
                '$filter': {
                  'input': '$$sa.fileArray',
                  'as': 'sn',
                  'cond': {
                    '$and': [

                      { '$eq': [ '$$sn.version', { '$$sn.version':     {'$slice': -1}} ]}

                    ]
                  }
                }
              }
            }
          },
        },
        'as': 'sa',
        'cond': {
          '$and': [
            { '$eq': [ '$$sa.color', 'blue' ] },
            { '$gt': [ { '$size': '$$sa.fileArray' }, 0 ] }
          ]
            }
          }
        }
       }}


       ]
      ,cursor : {batchSize : 1}
       }
         )

Throws an Error

       {
    "ok" : 0.0,
    "errmsg" : "Unrecognized expression '$$sn.version'",
    "code" : 168,
    "codeName" : "InvalidPipelineOperator"
      }

Expected Result :

      {
                "_id" : ObjectId("562e7c594c12942f08fe4192"),
                "inventory" : {
                    "id" : "I0001",
                    "name" : "Fish"
                },
                "shapes" : [
                    {
                        "color" : "blue",
                        "fileArray" : [
                            {
                                "fileid" : "ID003",
                                "filename" : "Abc.jpg",
                                "filepath" : "d:/temp/",
                                "version" : "3.0"
                            }
                        ]
                    }
                ]
            }

Please help where I am going wrong.

Regards
Kris

Wan Bachtiar

unread,

Dec 20, 2017, 1:41:00 AM12/20/17

to mongodb-user

I am using mongodb 3.4.2 and want to find out the maximum element in deep nested array in mongodb.

Hi Kris,

It’s been a while since you posted this question, have you found an answer yet ?

If you just would like to retrieve the highest value of fileArray.version, you can sort them.
For example:

db.collection.aggregate([
    {"$match"  : { "inventory.name":"Fish"} },
    {"$unwind" : "$shapes" }, 
    {"$unwind" : "$shapes.fileArray" }, 
    {"$sort"   : {"shapes.fileArray.version": -1} }, 
    {"$limit"  : 1 }
]);