Relay Cursor Spec Pagination in MongoDB

After + First

// see https://mongoplayground.net/p/hrVd5xrWBbh
db.collection.aggregate([
  {
    $match: {
      ...preconditions...
    }
  },
  {
    $sort: {
      _id: 1
    }
  },
  {
    $facet: {
      total: [
        {
          $count: "count"
        }
      ],
      before: [
        {
          $match: {
            _id: {
              $lt: cursor
            }
          }
        },
        {
          $count: "before"
        }
      ],
      data: [
        {
          $match: {
            _id: {
              $gte: cursor
            }
          }
        },
        {
          $limit: limit
        }
      ]
    }
  },
  {
    $project: {
      data: 1,
      "count.total": {
        $last: "$total.count"
      },
      "count.before": {
        $last: "$before.before"
      },
      "count.results": {
        $literal: limit
      },
      "count.after": {
        $subtract: [
          {
            $subtract: [
              {
                $last: "$total.count"
              },
              limit
            ]
          },
          {
            $last: "$before.before"
          }
        ]
      }
    }
  }
])

Before + After + First

db.collection.aggregate([
  {
    $match: {
      key: {
        $gte: 1
      }
    }
  },
  {
    $sort: {
      _id: 1
    }
  },
  {
    $facet: {
      total: [
        {
          $count: "count"
        }
      ],
      before: [
        {
          $match: {
            key: {
              $lt: after
            }
          }
        },
        {
          $count: "before"
        }
      ],
      data: [
        {
          $match: {
            key: {
              $gte: after,
              $lte: before
              
            }
          }
        },
        {
          $limit: first
        },
        {
          $group: {
            _id: null,
            count: {
              $sum: 1
            },
            data: {
              $push: "$$ROOT"
            }
          }
        }
      ]
    }
  },
  {
    $project: {
      data: {
        $last: "$data.data"
      },
      "count.total": {
        $last: "$total.count"
      },
      "count.before": {
        $last: "$before.before"
      },
      "count.results": {
        $last: "$data.count"
      },
      "count.after": {
        $subtract: [
          {
            $subtract: [
              {
                $last: "$total.count"
              },
              {
                $last: "$data.count"
              }
            ]
          },
          {
            $last: "$before.before"
          }
        ]
      }
    }
  }
])

Before + After + Last

db.collection.aggregate([
  {
    $match: {
      key: {
        $gte: 1
      }
    }
  },
  {
    $sort: {
      _id: -1
    }
  },
  {
    $facet: {
      total: [
        {
          $count: "count"
        }
      ],
      after: [
        {
          $match: {
            key: {
              $gt: 20
            }
          }
        },
        {
          $count: "after"
        }
      ],
      data: [
        {
          $match: {
            key: {
              $lte: 20,
              $gte: 20
            }
          }
        },
        {
          $limit: 10
        },
        {
          $sort: {
            _id: 1
          }
        },
        {
          $group: {
            _id: null,
            count: {
              $sum: 1
            },
            data: {
              $push: "$$ROOT"
            }
          }
        }
      ]
    }
  },
  {
    $project: {
      data: "$data.data",
      "count.total": {
        $last: "$total.count"
      },
      "count.after": {
        $last: "$after.after"
      },
      "count.results": {
        $last: "$data.count"
      },
      "count.before": {
        $subtract: [
          {
            $subtract: [
              {
                $last: "$total.count"
              },
              {
                $last: "$data.count"
              }
            ]
          },
          {
            $last: "$after.after"
          }
        ]
      }
    }
  }
])

Before + After

// see https://mongoplayground.net/p/ep32rkBbwn8
db.collection.aggregate([
  {
    $match: {
      ...preconditions...
    }
  },
  {
    $sort: {
      _id: 1
    }
  },
  {
    $facet: {
      total: [
        {
          $count: "count"
        }
      ],
      before: [
        {
          $match: {
            _id: {
              $lt: cursor.after
            }
          }
        },
        {
          $count: "before"
        }
      ],
      data: [
        {
          $match: {
            _id: {
              $gte: cursor.after,
              $lte: cursor.before
            }
          }
        },
        {
          $group: {
            _id: null,
            count: {
              $sum: 1
            },
            data: {
              $push: "$$ROOT"
            }
          }
        }
      ]
    }
  },
  {
    $project: {
      data: {
        $last: "$data.data"
      },
      "count.total": {
        $last: "$total.count"
      },
      "count.before": {
        $last: "$before.before"
      },
      "count.results": {
        $last: "$data.count"
      },
      "count.after": {
        $subtract: [
          {
            $subtract: [
              {
                $last: "$total.count"
              },
              {
                $last: "$data.count"
              }
            ]
          },
          {
            $last: "$before.before"
          }
        ]
      }
    }
  }
])

Before + Last

// see https://mongoplayground.net/p/6AlPSmoSKL7
db.collection.aggregate([
  {
    $match: {
      ...preconditions...
    }
  },
  {
    $sort: {
      _id: -1
    }
  },
  {
    $facet: {
      total: [
        {
          $count: "count"
        }
      ],
      after: [
        {
          $match: {
            _id: {
              $gt: cursor
            }
          }
        },
        {
          $count: "after"
        }
      ],
      data: [
        {
          $match: {
            _id: {
              $lte: cursor
            }
          }
        },
        {
          $limit: limit
        },
        {
          $sort: {
            _id: 1
          }
        }
      ]
    }
  },
  {
    $project: {
      data: 1,
      "count.total": {
        $last: "$total.count"
      },
      "count.after": {
        $last: "$after.after"
      },
      "count.results": {
        $literal: limit
      },
      "count.before": {
        $subtract: [
          {
            $subtract: [
              {
                $last: "$total.count"
              },
              limit
            ]
          },
          {
            $last: "$after.after"
          }
        ]
      }
    }
  }
])

Return values

{
    "count": {
      "after": <int>,
      "before": <int>,
      "results": <int>,
      "total": <int>
    },
    "data": [.....]
}

Transformation to spec

{
 pageInfo: {
    startCursor: toCursor(data[0]._id),
    endCursor: toCursor(data[data.length-1]._id),
    hasPreviousPage: count.before > 0,
    hasNextPage: count.after > 0,
  },
edges: [
    {
      node: data[i],
      cursor: toCursor(data[i]._id)
    }
    .....
  ]
}