How to add sort,limit and skip in the aggregate query shown below?

Question

This is my data structure

{
    "_id" : ObjectId("57f37f18517f72bc09ee7632"),
    "name" : "testdata",
    "createdBy" : "57f1fdef1d3c40141617d215",

    "transitionEnabled" : false,
    "status" : "active",
    "createdDateTime" : ISODate("2016-10-04T10:06:16.195Z"),
    "accounts" : [ 
        "57f37f75517f72bc09ee7634"
    ],
    "deliverables" : [],
    "risks" : [],
    "issues" : {
        "_id" : ObjectId("57f38398517f72bc09ee7680"),
        "title" : "test",
        "description" : "Delay in testing due to issues with Provider Finder dataload in the test region. This has impacted the production release planned for Sep 30th",
        "plannedStartDate" : ISODate("2016-09-01T00:00:00.000Z"),
        "plannedEndDate" : ISODate("2016-10-30T00:00:00.000Z"),
        "assignedTo" : "57f375ea517f72bc09ee762a",
        "createdBy" : ObjectId("57f375ea517f72bc09ee762a"),
        "likes" : 0,
        "createdDateTime" : ISODate("2016-10-04T10:25:28.301Z"),
        "status" : "open",
        "stakeholders" : [],
        "__v" : 0,
        "lastUpdatedTime" : ISODate("2019-11-15T09:19:06.279Z")
    },
    "__v" : 0
}

I would like to select all the issues group by the organization and I want to implement sort,limit and skip for those data(the sub array issues data only from the above). For that I've tried the following code

db.organizations.aggregate([ { "$lookup" : { "from" : "issues", "localField" : "issues.str", "foreignField" : "_id.str", "as" : "issues" } }, {$sort: {weight: -1, "issues.lastUpdatedTime": 1}} { $group: { _id: "$issues", }, }, ])

I am getting the result as follows.

How to sort and set limit and skip for the below query? Results returned by the query is also attached.

But I not need the outer _id field which shown in the above result. Please help me to solve this problem.

no need to show extra information just show what you have and what you want that would be better instead of image. explanation can be like https://stackoverflow.com/questions/38612972/how-to-merge-two-array-of-object-by-using-lodash — Shaishab Roy, Nov 20 '17 at 13:33

Shaishab Roy · Answer 1 · 2017-11-20T09:31:48.147

4

Your sort not working for field issues.lastUpdatedTime because issues is an array of object after $lookup just not a plain object. so you need to $unwind first then apply sort After sort you should use skip and limit. like

{$unwind:"$issues"},
{$sort: {weight: -1, "issues.lastUpdatedTime": 1}},
{ $skip: 10 },// set value as you need 
{ $limit : 50 }// set value as you need

NB: $limit always should be after $skip for aggregation. so code will be like

db.organizations.aggregate([
  {
    "$lookup": { "from": "issues", "localField": "issues.str", "foreignField": "_id.str", "as": "issues" }
  },
  {$unwind:"$issues"},
  {$sort: {weight: -1, "issues.lastUpdatedTime": 1}},
  {$skip: 10},
  {$limit: 50}
]);

NB: If you want to return fixed amount of document after grouping then you should use $skip and $limit anfter $group stage

edited Nov 20 '17 at 09:31

answered Nov 20 '17 at 06:41

Shaishab Roy

16,335
7
50
68

should be working. what wrong data are you getting ? – Shaishab Roy Nov 20 '17 at 06:58
Its not ordering the data wrt lastUpdatedTime and the whole records were showing after running this query. Skip and limit also not working. always showing the whole data – vijesh Nov 20 '17 at 08:51
I want to order the items inside the _id shown on the attached image above and also I want to implement skip and limit for those data. – vijesh Nov 20 '17 at 08:55
`skip` and `limit` applied for first level document not for nested documents – Shaishab Roy Nov 20 '17 at 09:28
I want to get the nested array elements (issues) to outside and add skip and limits for those array elements. Is there any way for doing that? – vijesh Nov 20 '17 at 12:10
could you please add example like what is your data and what is your expectation. that would be better to make solution @vijesh – Shaishab Roy Nov 20 '17 at 12:14
@ Shaishab Roy I have updated my question with the structure of data. please check – vijesh Nov 20 '17 at 12:27

score 1 · Accepted Answer · answered Feb 01 '18 at 11:40

1

first $unwind the issues sub array and using its result its possible to apply sort, skip and limit

answered Feb 01 '18 at 11:40

vijesh

1,115
1
11
27

How to add sort,limit and skip in the aggregate query shown below?

2 Answers2