{ "explainVersion": "2", "stages": [ { "$cursor": { "queryPlanner": { "namespace": "myownDb.users_order", "indexFilterSet": false, "parsedQuery": { "$and": [ { "isDeleted": { "$eq": false } }, { "state": { "$in": [ "DRAFT", "PUBLISHED", "READY_FOR_PUBLISHING", "SCHEDULE_PUBLISH" ] } } ] }, "queryHash": "B45489EF", "planCacheKey": "A82CFE0F", "maxIndexedOrSolutionsReached": false, "maxIndexedAndSolutionsReached": false, "maxScansToExplodeReached": false, "winningPlan": { "queryPlan": { "stage": "GROUP", "planNodeId": 4, "inputStage": { "stage": "EQ_LOOKUP", "planNodeId": 3, "foreignCollection": "myownDb.orders", "localField": "_id", "foreignField": "userId", "asField": "orders", "strategy": "IndexedLoopJoin", "indexName": "userId_1", "indexKeyPattern": { "userId": 1 }, "inputStage": { "stage": "FETCH", "planNodeId": 2, "filter": { "$and": [ { "isDeleted": { "$eq": false } }, { "state": { "$in": [ "DRAFT", "PUBLISHED", "READY_FOR_PUBLISHING", "SCHEDULE_PUBLISH" ] } } ] }, "inputStage": { "stage": "IXSCAN", "planNodeId": 1, "keyPattern": { "createdAt": -1, "updatedAt": -1 }, "indexName": "createdAt_-1_updatedAt_-1", "isMultiKey": false, "multiKeyPaths": { "createdAt": [], "updatedAt": [] }, "isUnique": false, "isSparse": false, "isPartial": false, "indexVersion": 2, "direction": "forward", "indexBounds": { "createdAt": [ "[MaxKey, MinKey]" ], "updatedAt": [ "[MaxKey, MinKey]" ] } } } } }, "slotBasedPlan": { "slots": "$$RESULT=s49 env: { s2 = Nothing (SEARCH_META), s1 = TimeZoneDatabase(Europe/Malta...UCT) (timeZoneDB), s8 = {\"createdAt\" : -1, \"updatedAt\" : -1}, s14 = false, s3 = 1710044766417 (NOW), s15 = [\"SCHEDULE_PUBLISH\", \"PUBLISHED\", \"READY_FOR_PUBLISHING\", \"DRAFT\"] }", "stages": "[4] mkobj s49 [_id = s44, latestVersion = s45, versionCount = s47] true false \n[4] group [s44] [s45 = first((s42 ?: null)), s47 = sum(1)] spillSlots[s46, s48] mergingExprs[first((s46 ?: null)), sum(s48)] \n[4] project [s44 = (s43 ?: null)] \n[3] project [s43 = getField(s42, \"userId\")] \n[3] mkobj s42 s9 [] drop [orders = s41] true false \n[3] nlj inner [s9] [s21] \n left \n [3] nlj inner [s9] [s9] \n left \n [2] filter {(traverseF(s12, lambda(l1.0) { ((l1.0 == s14) ?: false) }, false) && traverseF(s13, lambda(l2.0) { isMember(l2.0, s15) }, false))} \n [2] nlj inner [] [s4, s5, s6, s7, s8] \n left \n [1] ixseek KS(0F0F0104) KS(F5F5FE04) s7 s4 s5 s6 lowPriority [] @\"0e866e4f-1945-43b4-86fd-9fd9c3b32302\" @\"createdAt_-1_updatedAt_-1\" true \n right \n [2] limit 1 \n [2] seek s4 s9 s10 s5 s6 s7 s8 [s11 = userId, s12 = isDeleted, s13 = state] @\"0e866e4f-1945-43b4-86fd-9fd9c3b32302\" true false \n right \n [3] project [s21 = \n if isArrayEmpty(s19) \n then [null] \n else s19 \n ] \n [3] group [] [s19 = addToSet(s17)] spillSlots[s20] mergingExprs[aggSetUnion(s20)] \n [3] unwind s17 s18 s16 true \n [3] project [s16 = getField(s9, \"_id\")] \n [3] limit 1 \n [3] coscan \n right \n [3] limit 1 \n [3] union [s41] \n branch0 [s39] \n [3] project [s39 = getElement(s37, 0)] \n [3] group [] [s37 = addToArrayCapped(s35, 104857600)] spillSlots[s38] mergingExprs[aggConcatArraysCapped(s38, 104857600)] \n [3] filter {traverseF((getField(s35, \"userId\") ?: null), lambda(l3.0) { isMember(l3.0, s21) }, true)} \n [3] nlj inner [] [s31, s33, s34, s32, s30] \n left \n [3] nlj inner [s30] [s28, s29] \n left \n [3] nlj inner [] [s22] \n left \n [3] unwind s22 s23 s21 true \n [3] limit 1 \n [3] coscan \n right \n [3] project [s28 = ks(1ll, 0, s27, 1ll), s29 = ks(1ll, 0, s27, 2ll), s30 = {\"userId\" : 1}] \n [3] union [s27] \n branch0 [s24] \n [3] cfilter {isNull(s22)} \n [3] project [s24 = undefined] \n [3] limit 1 \n [3] coscan \n branch1 [s25] \n [3] filter {(isArray(s22) && !(isMember(s25, s21)))} \n [3] project [s25 = (getElement(s22, 0) ?: undefined)] \n [3] limit 1 \n [3] coscan \n branch2 [s26] \n [3] project [s26 = s22] \n [3] limit 1 \n [3] coscan \n right \n [3] ixseek s28 s29 s32 s31 s33 s34 [] @\"3d87bccc-263b-4098-adfe-5295126aadb9\" @\"userId_1\" true \n right \n [3] limit 1 \n [3] seek s31 s35 s36 s33 s34 s32 s30 [] @\"3d87bccc-263b-4098-adfe-5295126aadb9\" true false \n branch1 [s40] \n [3] project [s40 = []] \n [3] limit 1 \n [3] coscan \n" } }, "rejectedPlans": [ { "queryPlan": { "stage": "GROUP", "planNodeId": 5, "inputStage": { "stage": "EQ_LOOKUP", "planNodeId": 4, "foreignCollection": "myownDb.orders", "localField": "_id", "foreignField": "userId", "asField": "orders", "strategy": "IndexedLoopJoin", "indexName": "userId_1", "indexKeyPattern": { "userId": 1 }, "inputStage": { "stage": "SORT", "planNodeId": 3, "sortPattern": { "createdAt": -1, "updatedAt": -1 }, "memLimit": 104857600, "type": "simple", "inputStage": { "stage": "FETCH", "planNodeId": 2, "filter": { "isDeleted": { "$eq": false } }, "inputStage": { "stage": "IXSCAN", "planNodeId": 1, "keyPattern": { "state": 1 }, "indexName": "state_1", "isMultiKey": false, "multiKeyPaths": { "state": [] }, "isUnique": false, "isSparse": false, "isPartial": false, "indexVersion": 2, "direction": "forward", "indexBounds": { "state": [ "[\"DRAFT\", \"DRAFT\"]", "[\"PUBLISHED\", \"PUBLISHED\"]", "[\"READY_FOR_PUBLISHING\", \"READY_FOR_PUBLISHING\"]", "[\"SCHEDULE_PUBLISH\", \"SCHEDULE_PUBLISH\"]" ] } } } } } }, "slotBasedPlan": { "slots": "$$RESULT=s68 env: { s2 = Nothing (SEARCH_META), s9 = {\"state\" : 1}, s3 = 1710044766417 (NOW), s31 = Nothing, s5 = Nothing, s13 = Nothing, s1 = TimeZoneDatabase(Europe/Malta...UCT) (timeZoneDB), s24 = Nothing }", "stages": "[5] mkobj s68 [_id = s63, latestVersion = s64, versionCount = s66] true false \n[5] group [s63] [s64 = first((s61 ?: null)), s66 = sum(1)] spillSlots[s65, s67] mergingExprs[first((s65 ?: null)), sum(s67)] \n[5] project [s63 = (s62 ?: null)] \n[4] project [s62 = getField(s61, \"userId\")] \n[4] mkobj s61 s25 [] drop [orders = s60] true false \n[4] nlj inner [s25] [s40] \n left \n [4] nlj inner [s25] [s25] \n left \n [3] sort [s33, s34] [desc, desc] [s25, s27] \n [3] project [s33 = \n if (isArray(s28) ?: false) \n then (_internalGreatest(s28) ?: undefined) \n else (s28 ?: null) \n , s34 = \n if (isArray(s29) ?: false) \n then (_internalGreatest(s29) ?: undefined) \n else (s29 ?: null) \n ] \n [3] project [s32 = (!((isArray(s28) ?: false)) || (!((isArray(s29) ?: false)) || fail(2, \"cannot sort with keys that are parallel arrays\")))] \n [2] filter {traverseF(s30, lambda(l1.0) { ((l1.0 == s31) ?: false) }, false)} \n [2] nlj inner [] [s19, s20, s21, s22, s23] \n left \n [1] branch {s24} [s19, s20, s21, s22, s23] \n [s4, s6, s7, s8, s9] [1] ixscan_generic s5 s8 s4 s6 s7 lowPriority [] @\"0e866e4f-1945-43b4-86fd-9fd9c3b32302\" @\"state_1\" true \n [s10, s16, s17, s18, s9] [1] nlj inner [] [s11, s12] \n left \n [1] project [s11 = getField(s14, \"l\"), s12 = getField(s14, \"h\")] \n [1] unwind s14 s15 s13 false \n [1] limit 1 \n [1] coscan \n right \n [1] ixseek s11 s12 s18 s10 s16 s17 [] @\"0e866e4f-1945-43b4-86fd-9fd9c3b32302\" @\"state_1\" true \n right \n [2] limit 1 \n [2] seek s19 s25 s26 s20 s21 s22 s23 [s27 = userId, s28 = createdAt, s29 = updatedAt, s30 = isDeleted] @\"0e866e4f-1945-43b4-86fd-9fd9c3b32302\" true false \n right \n [4] project [s40 = \n if isArrayEmpty(s38) \n then [null] \n else s38 \n ] \n [4] group [] [s38 = addToSet(s36)] spillSlots[s39] mergingExprs[aggSetUnion(s39)] \n [4] unwind s36 s37 s35 true \n [4] project [s35 = getField(s25, \"_id\")] \n [4] limit 1 \n [4] coscan \n right \n [4] limit 1 \n [4] union [s60] \n branch0 [s58] \n [4] project [s58 = getElement(s56, 0)] \n [4] group [] [s56 = addToArrayCapped(s54, 104857600)] spillSlots[s57] mergingExprs[aggConcatArraysCapped(s57, 104857600)] \n [4] filter {traverseF((getField(s54, \"userId\") ?: null), lambda(l2.0) { isMember(l2.0, s40) }, true)} \n [4] nlj inner [] [s50, s52, s53, s51, s49] \n left \n [4] nlj inner [s49] [s47, s48] \n left \n [4] nlj inner [] [s41] \n left \n [4] unwind s41 s42 s40 true \n [4] limit 1 \n [4] coscan \n right \n [4] project [s47 = ks(1ll, 0, s46, 1ll), s48 = ks(1ll, 0, s46, 2ll), s49 = {\"userId\" : 1}] \n [4] union [s46] \n branch0 [s43] \n [4] cfilter {isNull(s41)} \n [4] project [s43 = undefined] \n [4] limit 1 \n [4] coscan \n branch1 [s44] \n [4] filter {(isArray(s41) && !(isMember(s44, s40)))} \n [4] project [s44 = (getElement(s41, 0) ?: undefined)] \n [4] limit 1 \n [4] coscan \n branch2 [s45] \n [4] project [s45 = s41] \n [4] limit 1 \n [4] coscan \n right \n [4] ixseek s47 s48 s51 s50 s52 s53 [] @\"3d87bccc-263b-4098-adfe-5295126aadb9\" @\"userId_1\" true \n right \n [4] limit 1 \n [4] seek s50 s54 s55 s52 s53 s51 s49 [] @\"3d87bccc-263b-4098-adfe-5295126aadb9\" true false \n branch1 [s59] \n [4] project [s59 = []] \n [4] limit 1 \n [4] coscan \n" } }, { "queryPlan": { "stage": "GROUP", "planNodeId": 5, "inputStage": { "stage": "EQ_LOOKUP", "planNodeId": 4, "foreignCollection": "orders.orders", "localField": "_id", "foreignField": "userId", "asField": "orders", "strategy": "IndexedLoopJoin", "indexName": "userIdId_1", "indexKeyPattern": { "userId": 1 }, "inputStage": { "stage": "SORT", "planNodeId": 3, "sortPattern": { "createdAt": -1, "updatedAt": -1 }, "memLimit": 104857600, "type": "simple", "inputStage": { "stage": "FETCH", "planNodeId": 2, "filter": { "state": { "$in": [ "DRAFT", "PUBLISHED", "READY_FOR_PUBLISHING", "SCHEDULE_PUBLISH" ] } }, "inputStage": { "stage": "IXSCAN", "planNodeId": 1, "keyPattern": { "isDeleted": 1 }, "indexName": "isDeleted_1", "isMultiKey": false, "multiKeyPaths": { "isDeleted": [] }, "isUnique": false, "isSparse": false, "isPartial": false, "indexVersion": 2, "direction": "forward", "indexBounds": { "isDeleted": [ "[false, false]" ] } } } } } }, "slotBasedPlan": { "slots": "$$RESULT=s54 env: { s5 = Nothing, s6 = Nothing, s1 = TimeZoneDatabase(Europe/Malta...UCT) (timeZoneDB), s10 = {\"isDeleted\" : 1}, s2 = Nothing (SEARCH_META), s17 = Nothing, s3 = 1710044766417 (NOW) }", "stages": "[5] mkobj s54 [_id = s49, latestVersion = s50, versionCount = s52] true false \n[5] group [s49] [s50 = first((s47 ?: null)), s52 = sum(1)] spillSlots[s51, s53] mergingExprs[first((s51 ?: null)), sum(s53)] \n[5] project [s49 = (s48 ?: null)] \n[4] project [s48 = getField(s47, \"userId\")] \n[4] mkobj s47 s11 [] drop [orders = s46] true false \n[4] nlj inner [s11] [s26] \n left \n [4] nlj inner [s11] [s11] \n left \n [3] sort [s19, s20] [desc, desc] [s11, s13] \n [3] project [s19 = \n if (isArray(s14) ?: false) \n then (_internalGreatest(s14) ?: undefined) \n else (s14 ?: null) \n , s20 = \n if (isArray(s15) ?: false) \n then (_internalGreatest(s15) ?: undefined) \n else (s15 ?: null) \n ] \n [3] project [s18 = (!((isArray(s14) ?: false)) || (!((isArray(s15) ?: false)) || fail(2, \"cannot sort with keys that are parallel arrays\")))] \n [2] filter {traverseF(s16, lambda(l1.0) { isMember(l1.0, s17) }, false)} \n [2] nlj inner [] [s4, s7, s8, s9, s10] \n left \n [1] cfilter {(exists(s5) && exists(s6))} \n [1] ixseek s5 s6 s9 s4 s7 s8 [] @\"0e866e4f-1945-43b4-86fd-9fd9c3b32302\" @\"isDeleted_1\" true \n right \n [2] limit 1 \n [2] seek s4 s11 s12 s7 s8 s9 s10 [s13 = userId, s14 = createdAt, s15 = updatedAt, s16 = state] @\"0e866e4f-1945-43b4-86fd-9fd9c3b32302\" true false \n right \n [4] project [s26 = \n if isArrayEmpty(s24) \n then [null] \n else s24 \n ] \n [4] group [] [s24 = addToSet(s22)] spillSlots[s25] mergingExprs[aggSetUnion(s25)] \n [4] unwind s22 s23 s21 true \n [4] project [s21 = getField(s11, \"_id\")] \n [4] limit 1 \n [4] coscan \n right \n [4] limit 1 \n [4] union [s46] \n branch0 [s44] \n [4] project [s44 = getElement(s42, 0)] \n [4] group [] [s42 = addToArrayCapped(s40, 104857600)] spillSlots[s43] mergingExprs[aggConcatArraysCapped(s43, 104857600)] \n [4] filter {traverseF((getField(s40, \"userId\") ?: null), lambda(l2.0) { isMember(l2.0, s26) }, true)} \n [4] nlj inner [] [s36, s38, s39, s37, s35] \n left \n [4] nlj inner [s35] [s33, s34] \n left \n [4] nlj inner [] [s27] \n left \n [4] unwind s27 s28 s26 true \n [4] limit 1 \n [4] coscan \n right \n [4] project [s33 = ks(1ll, 0, s32, 1ll), s34 = ks(1ll, 0, s32, 2ll), s35 = {\"userId\" : 1}] \n [4] union [s32] \n branch0 [s29] \n [4] cfilter {isNull(s27)} \n [4] project [s29 = undefined] \n [4] limit 1 \n [4] coscan \n branch1 [s30] \n [4] filter {(isArray(s27) && !(isMember(s30, s26)))} \n [4] project [s30 = (getElement(s27, 0) ?: undefined)] \n [4] limit 1 \n [4] coscan \n branch2 [s31] \n [4] project [s31 = s27] \n [4] limit 1 \n [4] coscan \n right \n [4] ixseek s33 s34 s37 s36 s38 s39 [] @\"3d87bccc-263b-4098-adfe-5295126aadb9\" @\"userId_1\" true \n right \n [4] limit 1 \n [4] seek s36 s40 s41 s38 s39 s37 s35 [] @\"3d87bccc-263b-4098-adfe-5295126aadb9\" true false \n branch1 [s45] \n [4] project [s45 = []] \n [4] limit 1 \n [4] coscan \n" } }, { "queryPlan": { "stage": "GROUP", "planNodeId": 5, "inputStage": { "stage": "EQ_LOOKUP", "planNodeId": 4, "foreignCollection": "myownDb.orders", "localField": "_id", "foreignField": "userId", "asField": "orders", "strategy": "IndexedLoopJoin", "indexName": "userId_1", "indexKeyPattern": { "userId": 1 }, "inputStage": { "stage": "SORT", "planNodeId": 3, "sortPattern": { "createdAt": -1, "updatedAt": -1 }, "memLimit": 104857600, "type": "simple", "inputStage": { "stage": "FETCH", "planNodeId": 2, "inputStage": { "stage": "IXSCAN", "planNodeId": 1, "keyPattern": { "state": 1, "isDeleted": 1 }, "indexName": "state_1_isDeleted_1", "isMultiKey": false, "multiKeyPaths": { "state": [], "isDeleted": [] }, "isUnique": false, "isSparse": false, "isPartial": false, "indexVersion": 2, "direction": "forward", "indexBounds": { "state": [ "[\"DRAFT\", \"DRAFT\"]", "[\"PUBLISHED\", \"PUBLISHED\"]", "[\"READY_FOR_PUBLISHING\", \"READY_FOR_PUBLISHING\"]", "[\"SCHEDULE_PUBLISH\", \"SCHEDULE_PUBLISH\"]" ], "isDeleted": [ "[false, false]" ], "contentCategory": [ "[MinKey, MaxKey]" ] } } } } } }, "slotBasedPlan": { "slots": "$$RESULT=s66 env: { s2 = Nothing (SEARCH_META), s9 = {\"state\" : 1, \"isDeleted\" : 1, \"contentCategory\" : 1}, s3 = 1710044766417 (NOW), s5 = Nothing, s13 = Nothing, s1 = TimeZoneDatabase(Europe/Malta...UCT) (timeZoneDB), s24 = Nothing }", "stages": "[5] mkobj s66 [_id = s61, latestVersion = s62, versionCount = s64] true false \n[5] group [s61] [s62 = first((s59 ?: null)), s64 = sum(1)] spillSlots[s63, s65] mergingExprs[first((s63 ?: null)), sum(s65)] \n[5] project [s61 = (s60 ?: null)] \n[4] project [s60 = getField(s59, \"userId\")] \n[4] mkobj s59 s25 [] drop [orders = s58] true false \n[4] nlj inner [s25] [s38] \n left \n [4] nlj inner [s25] [s25] \n left \n [3] sort [s31, s32] [desc, desc] [s25, s27] \n [3] project [s31 = \n if (isArray(s28) ?: false) \n then (_internalGreatest(s28) ?: undefined) \n else (s28 ?: null) \n , s32 = \n if (isArray(s29) ?: false) \n then (_internalGreatest(s29) ?: undefined) \n else (s29 ?: null) \n ] \n [3] project [s30 = (!((isArray(s28) ?: false)) || (!((isArray(s29) ?: false)) || fail(2, \"cannot sort with keys that are parallel arrays\")))] \n [2] nlj inner [] [s19, s20, s21, s22, s23] \n left \n [1] branch {s24} [s19, s20, s21, s22, s23] \n [s4, s6, s7, s8, s9] [1] ixscan_generic s5 s8 s4 s6 s7 lowPriority [] @\"0e866e4f-1945-43b4-86fd-9fd9c3b32302\" @\"state_1_isDeleted_1\" true \n [s10, s16, s17, s18, s9] [1] nlj inner [] [s11, s12] \n left \n [1] project [s11 = getField(s14, \"l\"), s12 = getField(s14, \"h\")] \n [1] unwind s14 s15 s13 false \n [1] limit 1 \n [1] coscan \n right \n [1] ixseek s11 s12 s18 s10 s16 s17 [] @\"0e866e4f-1945-43b4-86fd-9fd9c3b32302\" @\"state_1_isDeleted_1\" true \n right \n [2] limit 1 \n [2] seek s19 s25 s26 s20 s21 s22 s23 [s27 = userId, s28 = createdAt, s29 = updatedAt] @\"0e866e4f-1945-43b4-86fd-9fd9c3b32302\" true false \n right \n [4] project [s38 = \n if isArrayEmpty(s36) \n then [null] \n else s36 \n ] \n [4] group [] [s36 = addToSet(s34)] spillSlots[s37] mergingExprs[aggSetUnion(s37)] \n [4] unwind s34 s35 s33 true \n [4] project [s33 = getField(s25, \"_id\")] \n [4] limit 1 \n [4] coscan \n right \n [4] limit 1 \n [4] union [s58] \n branch0 [s56] \n [4] project [s56 = getElement(s54, 0)] \n [4] group [] [s54 = addToArrayCapped(s52, 104857600)] spillSlots[s55] mergingExprs[aggConcatArraysCapped(s55, 104857600)] \n [4] filter {traverseF((getField(s52, \"userId\") ?: null), lambda(l1.0) { isMember(l1.0, s38) }, true)} \n [4] nlj inner [] [s48, s50, s51, s49, s47] \n left \n [4] nlj inner [s47] [s45, s46] \n left \n [4] nlj inner [] [s39] \n left \n [4] unwind s39 s40 s38 true \n [4] limit 1 \n [4] coscan \n right \n [4] project [s45 = ks(1ll, 0, s44, 1ll), s46 = ks(1ll, 0, s44, 2ll), s47 = {\"userId\" : 1}] \n [4] union [s44] \n branch0 [s41] \n [4] cfilter {isNull(s39)} \n [4] project [s41 = undefined] \n [4] limit 1 \n [4] coscan \n branch1 [s42] \n [4] filter {(isArray(s39) && !(isMember(s42, s38)))} \n [4] project [s42 = (getElement(s39, 0) ?: undefined)] \n [4] limit 1 \n [4] coscan \n branch2 [s43] \n [4] project [s43 = s39] \n [4] limit 1 \n [4] coscan \n right \n [4] ixseek s45 s46 s49 s48 s50 s51 [] @\"3d87bccc-263b-4098-adfe-5295126aadb9\" @\"userId_1\" true \n right \n [4] limit 1 \n [4] seek s48 s52 s53 s50 s51 s49 s47 [] @\"3d87bccc-263b-4098-adfe-5295126aadb9\" true false \n branch1 [s57] \n [4] project [s57 = []] \n [4] limit 1 \n [4] coscan \n" } }, { "queryPlan": { "stage": "GROUP", "planNodeId": 5, "inputStage": { "stage": "EQ_LOOKUP", "planNodeId": 4, "foreignCollection": "myownDb.orders", "localField": "_id", "foreignField": "userId", "asField": "orders", "strategy": "IndexedLoopJoin", "indexName": "userId_1", "indexKeyPattern": { "userId": 1 }, "inputStage": { "stage": "SORT", "planNodeId": 3, "sortPattern": { "createdAt": -1, "updatedAt": -1 }, "memLimit": 104857600, "type": "simple", "inputStage": { "stage": "FETCH", "planNodeId": 2, "inputStage": { "stage": "IXSCAN", "planNodeId": 1, "keyPattern": { "state": 1, "isDeleted": 1 }, "indexName": "state_1_isDeleted_1", "isMultiKey": false, "multiKeyPaths": { "state": [], "isDeleted": [] }, "isUnique": false, "isSparse": false, "isPartial": false, "indexVersion": 2, "direction": "forward", "indexBounds": { "state": [ "[\"DRAFT\", \"DRAFT\"]", "[\"PUBLISHED\", \"PUBLISHED\"]", "[\"READY_FOR_PUBLISHING\", \"READY_FOR_PUBLISHING\"]", "[\"SCHEDULE_PUBLISH\", \"SCHEDULE_PUBLISH\"]" ], "isDeleted": [ "[false, false]" ] } } } } } }, "slotBasedPlan": { "slots": "$$RESULT=s66 env: { s1 = TimeZoneDatabase(Europe/Malta...UCT) (timeZoneDB), s5 = Nothing, s3 = 1710044766417 (NOW), s9 = {\"state\" : 1, \"isDeleted\" : 1}, s2 = Nothing (SEARCH_META), s24 = Nothing, s13 = Nothing }", "stages": "[5] mkobj s66 [_id = s61, latestVersion = s62, versionCount = s64] true false \n[5] group [s61] [s62 = first((s59 ?: null)), s64 = sum(1)] spillSlots[s63, s65] mergingExprs[first((s63 ?: null)), sum(s65)] \n[5] project [s61 = (s60 ?: null)] \n[4] project [s60 = getField(s59, \"userId\")] \n[4] mkobj s59 s25 [] drop [orders = s58] true false \n[4] nlj inner [s25] [s38] \n left \n [4] nlj inner [s25] [s25] \n left \n [3] sort [s31, s32] [desc, desc] [s25, s27] \n [3] project [s31 = \n if (isArray(s28) ?: false) \n then (_internalGreatest(s28) ?: undefined) \n else (s28 ?: null) \n , s32 = \n if (isArray(s29) ?: false) \n then (_internalGreatest(s29) ?: undefined) \n else (s29 ?: null) \n ] \n [3] project [s30 = (!((isArray(s28) ?: false)) || (!((isArray(s29) ?: false)) || fail(2, \"cannot sort with keys that are parallel arrays\")))] \n [2] nlj inner [] [s19, s20, s21, s22, s23] \n left \n [1] branch {s24} [s19, s20, s21, s22, s23] \n [s4, s6, s7, s8, s9] [1] ixscan_generic s5 s8 s4 s6 s7 lowPriority [] @\"0e866e4f-1945-43b4-86fd-9fd9c3b32302\" @\"state_1_isDeleted_1\" true \n [s10, s16, s17, s18, s9] [1] nlj inner [] [s11, s12] \n left \n [1] project [s11 = getField(s14, \"l\"), s12 = getField(s14, \"h\")] \n [1] unwind s14 s15 s13 false \n [1] limit 1 \n [1] coscan \n right \n [1] ixseek s11 s12 s18 s10 s16 s17 [] @\"0e866e4f-1945-43b4-86fd-9fd9c3b32302\" @\"state_1_isDeleted_1\" true \n right \n [2] limit 1 \n [2] seek s19 s25 s26 s20 s21 s22 s23 [s27 = userId, s28 = createdAt, s29 = updatedAt] @\"0e866e4f-1945-43b4-86fd-9fd9c3b32302\" true false \n right \n [4] project [s38 = \n if isArrayEmpty(s36) \n then [null] \n else s36 \n ] \n [4] group [] [s36 = addToSet(s34)] spillSlots[s37] mergingExprs[aggSetUnion(s37)] \n [4] unwind s34 s35 s33 true \n [4] project [s33 = getField(s25, \"_id\")] \n [4] limit 1 \n [4] coscan \n right \n [4] limit 1 \n [4] union [s58] \n branch0 [s56] \n [4] project [s56 = getElement(s54, 0)] \n [4] group [] [s54 = addToArrayCapped(s52, 104857600)] spillSlots[s55] mergingExprs[aggConcatArraysCapped(s55, 104857600)] \n [4] filter {traverseF((getField(s52, \"userId\") ?: null), lambda(l1.0) { isMember(l1.0, s38) }, true)} \n [4] nlj inner [] [s48, s50, s51, s49, s47] \n left \n [4] nlj inner [s47] [s45, s46] \n left \n [4] nlj inner [] [s39] \n left \n [4] unwind s39 s40 s38 true \n [4] limit 1 \n [4] coscan \n right \n [4] project [s45 = ks(1ll, 0, s44, 1ll), s46 = ks(1ll, 0, s44, 2ll), s47 = {\"userId\" : 1}] \n [4] union [s44] \n branch0 [s41] \n [4] cfilter {isNull(s39)} \n [4] project [s41 = undefined] \n [4] limit 1 \n [4] coscan \n branch1 [s42] \n [4] filter {(isArray(s39) && !(isMember(s42, s38)))} \n [4] project [s42 = (getElement(s39, 0) ?: undefined)] \n [4] limit 1 \n [4] coscan \n branch2 [s43] \n [4] project [s43 = s39] \n [4] limit 1 \n [4] coscan \n right \n [4] ixseek s45 s46 s49 s48 s50 s51 [] @\"3d87bccc-263b-4098-adfe-5295126aadb9\" @\"userId_1\" true \n right \n [4] limit 1 \n [4] seek s48 s52 s53 s50 s51 s49 s47 [] @\"3d87bccc-263b-4098-adfe-5295126aadb9\" true false \n branch1 [s57] \n [4] project [s57 = []] \n [4] limit 1 \n [4] coscan \n" } } ] } } }, { "$facet": { "paginatedResults": [ { "$internalFacetTeeConsumer": {} }, { "$limit": 10 } ], "queryDocumentCount": [ { "$internalFacetTeeConsumer": {} }, { "$group": { "_id": { "$const": "" }, "queryCount": { "$sum": { "$const": 1 } } } }, { "$project": { "queryCount": true, "_id": false } } ] } } ], "serverInfo": { "host": "Pradyumnas-MacBook-Pro.local", "port": 27017, "version": "7.0.2", "gitVersion": "02b3c655e1302209ef046da6ba3ef6749dd0b62a" }, "serverParameters": { "internalQueryFacetBufferSizeBytes": 104857600, "internalQueryFacetMaxOutputDocSizeBytes": 104857600, "internalLookupStageIntermediateDocumentMaxSizeBytes": 104857600, "internalDocumentSourceGroupMaxMemoryBytes": 104857600, "internalQueryMaxBlockingSortMemoryUsageBytes": 104857600, "internalQueryProhibitBlockingMergeOnMongoS": 0, "internalQueryMaxAddToSetBytes": 104857600, "internalDocumentSourceSetWindowFieldsMaxMemoryBytes": 104857600, "internalQueryFrameworkControl": "trySbeEngine" }, "command": { "aggregate": "Contents", "pipeline": [ { "$match": { "state": { "$in": [ "DRAFT", "PUBLISHED", "READY_FOR_PUBLISHING", "SCHEDULE_PUBLISH" ] }, "isDeleted": false } }, { "$lookup": { "from": "orders", "localField": "_id", "foreignField": "userId", "as": "orders" } }, { "$sort": { "createdAt": -1, "updatedAt": -1 } }, { "$group": { "_id": "$userId", "latestVersion": { "$first": "$$ROOT" }, "versionCount": { "$sum": 1 } } }, { "$facet": { "paginatedResults": [ { "$skip": 0 }, { "$limit": 10 } ], "queryDocumentCount": [ { "$count": "queryCount" } ] } } ], "cursor": {}, "$db": "myownDb" }, "ok": 1 }