Total size of documents matching pipeline’s $lookup stage exceeds 104857600 bytes

Working with Data
1

I have 2 collections. One for FormSubmissions and other for FormAnswers.

I want find out Total No of Submission by given FormId

1.FormSubmissions - (No of Documents = 17,318)
2.FormAnswers - (No of Documents = 17,191)

Sample data for FormSubmissions.

{
	"_id":{"$oid":"5e27ea5707f2a21cecb0dc77"},
	"LanguageId":{"$oid":"5a6304ffc3c3f119fc0e60c9"},
	"FormId":{"$oid":"5da7f7dfa6334912bc30a3f5"},
	"SubscriberId":{"$oid":"5db1278dc6f6570e9121edad"},
	"FormVersionId":{"$oid":"5da7f7dfa6334912bc30a3f6"},
	"FolderName":"7ff60e99-ab67-75d8-d2e7-e2c93f26bcfd",
	"IsSync":false,
	"UpdatedBy":0,
	"IsCompleted":false,
	"ProjectId":{"$oid":"5e25966d07f2a21cecb0cd27"},
	"ClientId":{"$oid":"5e258f9807f2a21cecb0cd23"},
	"LocalInLineId":null,
	"ParentSubmissionId":null,
	"IsDeleted":true,"UpdatedOn":{"$date":"2020-02-03T07:40:42.626Z"},
	"SubmittedOn":{"$date":"2020-01-22T06:23:19.237Z"},
	"CreatedOn":{"$date":"2020-01-22T06:23:19.237Z"},
	"EmployeeId":{"$oid":"5db12946c6f6570e9121edb2"},
	"Longitude":"72.5298174",
	"Latitude":"23.0172479",
	"DeviceId":"b896c36665a93cb6",
	"AppInfoId":null,
	"__v":0
}

{
	"_id":{"$oid":"5e340be407f2a21cecb2663e"},
	"LanguageId":{"$oid":"5a6304ffc3c3f119fc0e60c9"},
	"FormId":{"$oid":"5da7f7dfa6334912bc30a3f5"},
	"SubscriberId":{"$oid":"5db1278dc6f6570e9121edad"},
	"FormVersionId":{"$oid":"5e27f4a807f2a21cecb0dcf5"},
	"FolderName":"6450638c-0d3f-f406-1a2e-6ca2762a4a40",
	"IsSync":false,
	"UpdatedBy":0,
	"IsCompleted":false,
	"ProjectId":{"$oid":"5e25966d07f2a21cecb0cd27"},
	"ClientId":{"$oid":"5e258f9807f2a21cecb0cd23"},
	"LocalInLineId":null,
	"ParentSubmissionId":null,
	"IsDeleted":true,
	"UpdatedOn":{"$date":"2020-02-03T07:40:55.157Z"},
	"SubmittedOn":{"$date":"2020-01-31T11:13:40.410Z"},
	"CreatedOn":{"$date":"2020-01-31T11:13:40.410Z"},
	"EmployeeId":{"$oid":"5e2fd5a607f2a21cecb15bba"},
	"Longitude":"72.5298021",
	"Latitude":"23.0172461",
	"DeviceId":"b4e958698555a31d",
	"AppInfoId":null,
	"__v":0
}

and sample data for FormAnswers

{
	"_id":{"$oid":"5e27ea5707f2a21cecb0dcc1"},
	"FormId":{"$oid":"5da7f7dfa6334912bc30a3f5"},
	"FormVersionId":{"$oid":"5da7f7dfa6334912bc30a3f6"},
	"FormSubmissionId":{"$oid":"5e27ea5707f2a21cecb0dc77"},
	"LanguageId":{"$oid":"5a6304ffc3c3f119fc0e60c9"},
	"Answers":[{
			"_id":{"$oid":"5e27ea5707f2a21cecb0dc78"},
			"FormQuestionId":{"$oid":"5e27e1b607f2a21cecb0daf5"},
			"ElementType":8,
			"Value":"ABC"
		},
		{
			"_id":{"$oid":"5e27ea5707f2a21cecb0dc79"},
		    "FormQuestionId":{"$oid":"5e27e1b607f2a21cecb0daf6"},
			"ElementType":2,
			"Value":"3"
	    },
	    {
			"_id":{"$oid":"5e27ea5707f2a21cecb0dc7a"},
			"FormQuestionId":{"$oid":"5e27e1b607f2a21cecb0daf7"},
			"ElementType":45,
			"Value":"22012020/114920"
	    },],
	"__v":0
}

Here is the query:

db.FormSubmissions.aggregate([
  {
    $match: {
      $and: [{
             FormId: ObjectId('5da7f7dfa6334912bc30a3f5')
           }, 
		   {
             IsDeleted: false
		   }]
    },
  },
  {
    $group: {
      _id: null,
      submissionIds: {
        $addToSet: '$_id',
      },
    },
  },
  {
    $lookup: {
      from: 'FormAnswers',
      localField: 'submissionIds',
      foreignField: 'FormSubmissionId',
      as: 'FormAnswers',
    },
  },
  {
    $project: {
      _id: null,
      TotalSubmissions: {
        $size: '$FormAnswers',
      },
    },
  },
])

When Execute above query it give below error
Total size of documents in FormAnswers matching pipeline’s $lookup stage exceeds 104857600 bytes

Please provide me best solutions for above query.

Thanks.

2

What about running a similar pipeline from the other collection? @hiren_parejiya

It seems that the block you’ll move over with a $lookup will be smaller, because the average document size seems smaller, and the number of documents is quite similar.

This is the idea:

Take all documents in the formAnswers collection, and do a graph lookup like this:

{
$graphLookup : {
from:"formSubmissions",
startWith:"$formSubmissionId",
connectToField:"_id",
connectFromField:"formSubmissionId",
as:"find",
maxDepth:0,
restrictSearchWithMatch : { 
FormId:ObjectId('5da7f7dfa6334912bc30a3f5'), 
isDeleted:false 
}
}}

It’s not completed yet. Think grouping of some sort is needed afterwards.