Home/ Questions/Q 1388
In Process
Gary Christan
Asked: 2022-02-19T08:30:27+00:00

Extract ranges from sequential values

Task 1:

I have my collection with documents in mongodb with value from sequential ranges as follow :

 {x:1}
 {x:2}
 {x:3}
 {x:5}
 {x:6}
 {x:7}
 {x:8}
 {x:20}
 {x:21}

I need to extract a list of sequential ranges in the form(the count is not compulsory , but I need at least the first and last values from the range):

 {x:[1,3] , count:3}
 {x:[5,8], count:4}
 {x:[20,21],count:2}

or

 { min:1 , max:3 , count:3}
 { min:5 , max:8 , count:4}
 { min:20 , max:21 , count:2}

Please, advice suitable solution , collection has ~100M docs , some of the values are in 10 digit ranges others in 15 digit ranges , but they are all sequentially incremental in their range?

Task 2:

Same think like in Task 1 , but taken based on custom sequence step , for example if the sequence step is 3:

{y:1}
{y:3}
{y:5}
{y:20}
{y:22}

need to produce:

{y:[1,5] ,count:3}
{y:[20,22]} , count:2}

Thanks!

P.S. I succeeded partially to get some ranges picture by fetch distribution by number of digits range , but this seems to be very general:

 db.collection.aggregate([
   {
     $addFields: {
       range: {
         $strLenCP: {
           $toString: "$x"
         }
       }
     }
   },
   {
     $group: {
       _id: "$range",
       minValue: {
         $min: "$x"
       },
       maxValue: {
         $max: "$x"
       },
       Count: {
         $sum: 1
       }
     }
   },
   {
     $addFields: {
       x: [
         {
           $toString: "$minValue"
         },
         {
           $toString: "$maxValue"
         }
       ]
     }
   },
   {
     $project: {
       range: "$_id",
       "_id": 0,
       x: 1,
       Count: 1
     }
   },
   {
     $sort: {
       range: 1
     }
   }
 ])
mongodbpython
Share

1 Answer

  1. using $reduce

    if i’m not mistaken for task2 just change 1 in $cond, $ne to any sequence step you want

    playground

    db.collection.aggregate([
  {
    "$sort": {
      x: 1
    }
  },
  {
    $group: {
      _id: null,
      temp: {
        $push: "$$ROOT"
      }
    }
  },
  {
    "$project": {
      _id: 0,
      "temp_field": {
        "$reduce": {
          "input": "$temp",
          "initialValue": {
            "prev": -999999,
            "min": -999999,
            "count": 0,
            "ranges": []
          },
          "in": {
            "prev": "$$this.x",
            "count": {
              "$cond": [
                {
                  $gt: [
                    {
                      "$subtract": [
                        "$$this.x",
                        "$$value.prev"
                      ]
                    },
                    1//sequence step
                    
                  ],
                  
                },
                1,
                {
                  "$add": [
                    "$$value.count",
                    1
                  ]
                }
              ]
            },
            "min": {
              "$cond": [
                {
                  $gt: [
                    {
                      "$subtract": [
                        "$$this.x",
                        "$$value.prev"
                      ]
                    },
                    1//sequence step
                    
                  ],
                  
                },
                "$$this.x",
                "$$value.min"
              ]
            },
            "ranges": {
              "$concatArrays": [
                "$$value.ranges",
                {
                  "$cond": [
                    {
                      $gt: [
                        {
                          "$subtract": [
                            "$$this.x",
                            "$$value.prev"
                          ]
                        },
                        1//sequence step
                        
                      ],
                      
                    },
                    [
                      {
                        max: "$$value.prev",
                        min: "$$value.min",
                        count: "$$value.count"
                      }
                    ],
                    []
                  ]
                }
              ]
            }
          }
        }
      }
    }
  },
  {
    "$project": {
      ranges: {
        "$concatArrays": [
          "$temp_field.ranges",
          [
            {
              max: "$temp_field.prev",
              min: "$temp_field.min",
              count: "$temp_field.count"
            }
          ]
        ]
      }
    }
  }
])

    and at the end pop the first element from array

    Comment by R2D2 after testing in the real use case I hit the memory limit with allowDiskUse: true:

       2022-02-14T09:38:27.575+0100 E QUERY    [js] Error: command failed: {
    "ok" : 0,
    "errmsg" : "$push used too much memory and cannot spill to disk. Memory limit: 104857600 bytes",
    "code" : 146,
    "codeName" : "ExceededMemoryLimit",

    Increased the memory to 2GB ( max allowed ) with:

     db.adminCommand({setParameter:1 ,  internalQueryMaxPushBytes: 2048576000 })

    But still faced the limit , then decided to split the collection to small ones so finally got my results , thank you once again!

    • 0

Leave an answer

You must login to add an answer.

Need An Account,