Finding ranges of continuous values

user13981607 0
Viewed 35

I have the following Mongo collection:

[
  {
    "key": 1,
    "user": "A",
    "comment": "commentA1"
  },
  {
    "key": 2,
    "user": "A",
    "comment": "commentA2"
  },
  {
    "key": 5,
    "user": "A",
    "comment": "commentA5"
  },
  {
    "key": 2,
    "user": "B",
    "comment": "commentB2"
  },
  {
    "key": 3,
    "user": "B",
    "comment": "commentB3"
  },
  {
    "key": 6,
    "user": "B",
    "comment": "commentB6"
  }
]

and I need to find the first continuous keys, with no gaps, per user. So, for user A I should get the first 2 documents, and for user B the first two also. The collection might contain more than 2M documents, so the query should work fast.

I have found SQL solutions for this problem (http://www.silota.com/docs/recipes/sql-gap-analysis-missing-values-sequence.html in section number 3), but I am looking for a Mongo solution.

How can I do it in Mongo 4.0 (DocumentDB) ?

continuous mongodb
1 Answers

One option is:

db.collection.aggregate([
  {$sort: {"key": 1}},
  {$group: {_id: "$user", data: {key: "$key", comment: "$comment"}}},
  {$set: {
      secondInx: {
        $first: {
          $reduce: {
            input: {$range: [1, {$size: "$data"}]},
            initialValue: [],
            in: {$concatArrays: ["$$value", 
                  {$cond: [
                    {$eq: [
                      {$subtract: [
                        {$arrayElemAt: ["$data.key", "$$this"]},
                        {$arrayElemAt: ["$data.key", {$subtract: ["$$this", 1]}]}
                      ]}, 1]
                    },
                    ["$$this"],
                    []
                  ]
                }
              ]
            }
          }
        }
      }
    }
  },
  {$project: {data: [{$arrayElemAt: ["$data", "$secondInx"]},
        {$arrayElemAt: ["$data", {$subtract: ["$secondInx", 1]}]}]}},
  {$unwind: "$data"},
  {$project: {comment: "$data.comment", key: "$data.key"}}
])

See how it works on the playground example

nimrod serok0
Related