Problems using $pull with $in in an update operation

Working with Data
,
1

I have a collection with documents similar to this

/* 1 createdAt:12/01/2024, 14:04:08*/
{
	"_id" : ObjectId("65a0f9808e914c17aadc0aa2"),
	"admission_id" : 4500198,
	"subject_id" : 339,
	"unit_id" : 15502,
	"usm_id" : 13935,
	"acad_year" : 7,
	"quiz" : [
		{
			"id" : 9080,
			"published_at" : ISODate("2024-01-12T14:08:46.288+05:30"),
			"updated_at" : ISODate("2024-01-12T14:08:46.288+05:30"),
			"complete_by" : ISODate("2024-01-17T14:08:46.288+05:30"),
			"total_questions" : "5",
			"correct" : 0,
			"wrong" : 0,
			"started_at" : null,
			"completed_at" : null
		}
	],
	"school_subject_id" : 35646623,
	"shell_name" : "Chapter",
	"shell_number" : "4",
	"subject_name" : "Math",
	"unitno" : 7,
	"xp_version" : 2
},

/* 2 createdAt:12/01/2024, 12:08:20*/
{
	"_id" : ObjectId("65a0de5c8e914c17aad338f0"),
	"admission_id" : 4500198,
	"subject_id" : 333,
	"unit_id" : 17576,
	"usm_id" : 16014,
	"acad_year" : 7,
	"quiz" : [
		{
			"id" : 9410,
			"published_at" : ISODate("2024-01-12T12:12:58.021+05:30"),
			"updated_at" : ISODate("2024-01-12T12:12:58.021+05:30"),
			"complete_by" : ISODate("2024-01-17T12:12:58.021+05:30"),
			"total_questions" : "5",
			"correct" : 0,
			"wrong" : 0,
			"started_at" : null,
			"completed_at" : null
		}
	],
	"school_subject_id" : 35541934,
	"shell_name" : "Chapter",
	"shell_number" : "2",
	"subject_name" : "Chemistry",
	"unitno" : 3,
	"xp_version" : 2
}

I have a list of admission_id and a list of quiz ids. For every admission_id, i have to delete the element in quiz array if its id falls in my provided array. I also have a unique index on

{
	"v" : 2,
	"key" : {
		"admission_id" : 1,
		"quiz.id" : 1
	},
	"name" : "admission_id_1_quiz.id_1",
	"unique" : true,
	"indexSize" : 123969536,

For deletion, i use this query

db.student_quiz.update({
    'admission_id': {
        '$in': [4500481, 4500464, 4500465, 4500466, 4500468, 4500469, 4500471, 4500472, 4500473, 4500474, 4500475, 4500476, 4500477, 4500478, 4500479, 4500460, 4500461, 4500462, 4500463, 4500458, 4500470, 4500459, 4500453, 4500454, 4500455, 4500456, 4500399, 4651955, 4500394, 4500395, 4500396, 4500397, 4500413, 4500417, 4500416, 4500414, 4500418, 4500419, 4500405, 4500420, 4500421, 4500422, 4500403, 4500404, 4500406, 4500411, 4500410, 4500402, 4500412, 5932443, 5932437, 5932435, 5932433, 5264783, 5264779, 5122961, 4500535, 4500491, 4500492, 4500383, 4500379, 4500389, 4500486, 4500500, 4500494, 4500505, 4500485, 4500501, 4500502, 4500483, 4500482, 4500484, 4500490, 4500503, 4500493, 4500489, 4500506, 4500498, 4500499, 4500385, 4500378, 4500384, 4500390, 4500387, 4500386, 4500382, 4500393, 4500388, 4500375, 4500391, 4500380, 4500377, 4500206, 4500215, 4500222, 4500219, 5123982, 4500226, 4500507, 4500495, 4500488, 4500504, 4500496, 4500225, 4500221, 4500205, 4500198, 4500208, 4500213, 4500201, 4500204, 4500203, 4500218, 4500220, 4500202, 4500224, 4500212, 4500211, 4500207, 4500200, 5653669, 5653666, 4500223, 4500508, 4500487, 4500361, 4500349, 4500366, 4500351, 4500370, 4500357, 4500356, 4500374, 4500365, 4500368, 4500359, 4500352, 4500353, 4500363, 4500358, 4500345, 4500371, 4500362, 4500373, 4500347, 4500346, 4500343, 4500344, 5122962, 4500369, 4500354, 4500355, 4500350, 4500348, 4500372, 4500364, 4500367, 4500360, 4500326, 4500301, 4500310, 4500309, 4500291, 4500311, 4500308, 4500304, 4500288, 4500298, 4500295, 4500293, 4500290, 4500289, 4500305, 4500292, 4500303, 4500296, 4500306, 4500286, 4500287, 4500299, 4500294, 4500302, 4500300, 4500285, 4500275, 4500270, 4500263, 4500269, 4500266, 4500281, 4500261, 4500276, 4500267, 4500271, 4500264, 4500283, 4500265, 4500284, 4500268, 4500262, 4500282, 4500277, 4500274, 4500280, 5940028, 5940025, 5940022, 5940021, 5940017, 5932431, 5932430, 5932425, 4500439, 4500440, 4500441, 4500442, 4500443, 4500444, 4500445, 4500423, 4500425, 4500427, 4500429, 4500430, 4500431, 4500432, 4500433, 4500434, 4500435, 4500437, 4500438, 4500446, 4500447, 4500448, 4500449, 4500450, 4500451, 5701681, 4500323, 4500314, 4500538, 4500512, 5278817, 4500515, 4500516, 4500518, 4500520, 4500521, 4500522, 4500523, 4500524, 4500525, 4500526, 4500537, 4500527, 4500528, 4500530, 4500532, 4500534, 4500536, 4500513, 4500533, 5266978, 5264784, 5932451, 4500545, 4500544, 4500543, 4500542, 4500541, 4500540, 4500539, 4500570, 4500569, 4500565, 4500566, 4500312, 4500606, 4500575, 4500574, 4500573, 4500572, 4500568, 4500567, 4500603, 4500576, 4500594, 4500593, 4500580, 4500581, 4500582, 4500548, 4500556, 4500552, 4500551, 4500550, 4500547, 4500546, 4500555, 4500564, 4500563, 4500562, 4500561, 5702162, 4500571, 4500559, 4500560, 4500244, 4500246, 4500234, 4500229, 4500199, 4500320, 4500338, 4500428, 4500436, 4500452, 4500401, 4500529, 4500531, 4500557, 4500407, 4500409, 4500217, 4500553, 4500424, 4500426, 5653668, 5653670, 4500517, 4500592, 4500600, 4500519, 5253934, 5253955, 4500497, 5667615, 5667610, 5667612, 5667617, 5667619, 5667620, 5667621, 5667622, 5122960, 5122972, 4500216, 4500381, 5702160, 5702161, 5266979, 4500511, 4500558, 4500549, 4500467, 4500297, 4500214, 5278816, 5278818, 5123981, 5123979, 5416871, 4500210, 4500257, 4500256, 4500260, 4500255, 4500258, 4500259, 4500480, 4500510, 4500509, 4500604, 4500602, 4500601, 4500599, 4500598, 4500597, 4500596, 4500595, 4500586, 4500579, 4500578, 4500577, 4500376, 4500278, 4500273, 4500272, 4500457, 4500554, 4500587, 4500588, 4500589, 4500590, 4500591, 4500585, 4500584, 4500583, 4500209, 4500332, 4500408, 4500313, 6111338, 6111339, 5889497, 4500400, 4500415, 5908323, 5932420, 5932418, 5932416, 5932414, 5932413, 5932449, 5932447, 6120820, 4500392, 4500605, 4500514, 4500307, 6120835, 6120836, 6120837, 5122958, 6121189, 6121192, 6121185, 6121181, 6121206, 6122172, 6122176, 6122173, 6122164, 6122170, 4500398, 5122971, 5416872, 6122796, 6122799, 6122803, 6122800, 6122846, 6122850, 6122858, 4500321, 4500340, 4500327, 4500279, 6126267, 6126271, 6126827, 6126829, 6126831, 6126843, 6127025, 6127010, 6127012, 6127013, 6127015, 6127055, 6127056, 6127057, 6127094, 6127091, 6127090, 6127089, 6127088, 6127114, 6127113, 6127112, 6127111, 6127201, 6127195, 6127204, 6144082, 6144134, 6144135, 6144133, 6144132, 6144130, 6144136, 6144131, 4500330, 4500329, 4500339, 4500318, 4500322, 4500328, 4500324, 4500336, 4500316, 4500317, 4500325, 4500337, 4500334, 4500319, 4500342, 4500333, 4500331, 4500335, 5726687, 4500315, 4500341, 5123980, 6149970, 6149971, 6150458, 6150459, 6150460, 6150512, 6150513, 6150514, 6150516, 6150575, 6150661, 6150662, 6151839, 6154992, 6155000, 6155006, 6155007, 6155253, 6155254, 6156462, 6156790, 6156793, 6157179, 6157180, 6157181, 6157205, 6157207, 6157262, 6157265, 6157286, 4500237, 4500239, 4500247, 4500245, 4500240, 4500250, 4500251, 4500252, 4500253, 4500241, 4500232, 4500242, 6068243, 6120819, 6122159, 6122171, 6122165, 6122652, 4500243, 5122957, 4500254, 4500231, 4500249, 4500238, 6122818, 6122819, 6122825, 4500248, 4500197, 4500228, 4500236, 6122822, 6122823, 6122824, 6122826, 6122814, 6122815, 6122816, 6122817, 6122820, 6122829, 6122821, 6122813, 6122839, 6122837, 6122807, 6122836, 6122835, 6122830, 6122840, 6122838, 4500233, 6122831, 6122834, 6122812, 6122810, 6122809, 6122811, 6122808, 6122832, 6122833, 6122798, 6122806, 6122827, 6122828, 4500227, 6122842, 6122844, 6122843, 6122845, 6122847, 6122848, 6122849, 6122841, 6123959, 6123960, 6123958, 6123918, 6123957, 6123956, 6123915, 6123914, 6123935, 6123961, 6123965, 6123964, 6123962, 6123966, 6123973, 6123972, 6123971, 6123967, 6123968, 6123970, 6123969, 6123955, 6123963, 6123907, 6123925, 6123924, 6123923, 6123922, 6123951, 6123949, 6123950, 6123913, 6123920, 6123909, 6123910, 6123927, 6123911, 6123912, 6123926, 4500235, 6123928, 6123929, 6123930, 6123944, 6123943, 6123942, 6123941, 6123932, 6123946, 6123954, 6123953, 6123919, 6123916, 6123940, 6123936, 6123952, 6123939, 6123938, 6123908, 6123906, 6123931, 6123934, 6123917, 6123933, 6123921, 6123947, 6123948, 6123945, 6123937, 4500230, 6123975, 6123974, 6123978, 6123977, 6123979, 6123982, 6123990, 6123984, 6123989, 6123976, 6123991, 6123986, 6123980, 6123987, 6124004, 6123983, 6123981, 6123985, 6123988, 6123999, 6124000, 6123992, 6123997, 6123998, 6124013, 6124010, 6124009, 6124008, 6124007, 6124006, 6124005, 6124003, 6124016, 6123993, 6124014, 6124015, 6124012, 6123994, 6124002, 6123995, 6123996, 6124001, 6124011, 6125483, 6125489, 6125482, 6125485, 6125494, 6125492, 6125500, 6125499, 6125498, 6125480, 6125481, 6125490, 6125491, 6125502, 6125484, 6125488, 6125508, 6125507, 6125506, 6125503, 6125486, 6125493, 6125495, 6125496, 6125487, 6125509, 6125528, 6125523, 6125516, 6125510, 6125512, 6125515, 6125519, 6125525, 6125527, 6125497, 6125505, 6125518, 6125548, 6125520, 6125522, 6125517, 6125501, 6125513, 6125549, 6125511, 6125550, 6125504, 6125530, 6125531, 6125539, 6125537, 6125551, 6125543, 6125544, 6125514, 6125535, 6125536, 6125534, 6125547, 6125552, 6125553, 6125529, 6125554, 6125538, 6125521, 6125533, 6125542, 6125546, 6125540, 6125524, 6125541, 6125526, 6125532, 6125545, 6126269, 6126266, 6126270, 6126268, 6126285, 6126284, 6126283, 6126282, 6126275, 6126286, 6126302, 6126308, 6126310, 6126309, 6126307, 6126306, 6126289, 6126287, 6126300, 6126298, 6126291, 6126276, 6126277, 6126278, 6126280, 6126279, 6126305, 6126303, 6126304, 6126301, 6126311, 6126290, 6126288, 6126313, 6126297, 6126299, 6126296, 6126295, 6126294, 6126293, 6126292, 6126324, 6126315, 6126323, 6126320, 6126319, 6126330, 6126327, 6126328, 6126329, 6126281, 6126331, 6126314, 6126312, 6126322, 6126316, 6126318, 6126326, 6126325, 6126339, 6126332, 6126336, 6126333, 6126340, 6126334, 6126337, 6126335, 6126317, 6126321, 6126342, 6126360, 6126359, 6126358, 6126357, 6126346, 6126347, 6126345, 6126354, 6126355, 6126356, 6126364, 6126338, 6126365, 6126341, 6126343, 6126344, 6126353, 6126351, 6126350, 6126349, 6126361, 6126362, 6126363, 6126352, 6126348, 6126426, 6126444, 6126443, 6126442, 6126441, 6126449, 6126428, 6126431, 6126430, 6126429, 6126427, 6126432, 6126445, 6126447, 6126448, 6126435, 6126436, 6126437, 6126438, 6126440, 6126469, 6126454, 6126446, 6126467, 6126471, 6126461, 6126460, 6126459, 6126458, 6126457, 6126456, 6126455, 6126453, 6126473, 6126466, 6126464, 6126462, 6126465, 6126468, 6126470, 6126472, 6126474, 6126463, 6126439, 6126452, 6126451, 6126450, 6126274, 6126273, 6126272, 6126425, 6126433, 6126434, 6126621, 6126639, 6126638, 6126637, 6126636, 6126624, 6126634, 6126644, 6126623, 6126622, 6126627, 6126625, 6126626, 6126640, 6126629, 6126620, 6126628, 6126648, 6126647, 6126646, 6126645, 6126650, 6126651, 6126652, 6126641, 6126642, 6126643, 6126630, 6126631, 6126635, 6126633, 6126664, 6126667, 6126670, 6126672, 6126675, 6126680, 6126653, 6126655, 6126661, 6126654, 6126656, 6126663, 6126684, 6126682, 6126666, 6126669, 6126681, 6126674, 6126657, 6126676, 6126678, 6126679, 6126677, 6126658, 6126660, 6126662, 6126665, 6126668, 6126671, 6126659, 6126683, 6126673, 6126649, 6126722, 6126724, 6126723, 6126721, 6126720, 6126714, 6126719, 6126718, 6126703, 6126702, 6126701, 6126700, 6126699, 6126698, 6126717, 6126716, 6126715, 6126713, 6126712, 6126711, 6126710, 6126704, 6126697, 6126696, 6126695, 6126694, 6126693, 6126692, 6126691, 6126690, 6126689, 6126688, 6126687, 6126686, 6126685, 6126738, 6126739, 6126740, 6126729, 6126728, 6126727, 6126726, 6126725, 6126730, 6126731, 6126732, 6126733, 6126734, 6126735, 6126709, 6126705, 6126708, 6126706, 6126707, 6126741, 6126743, 6126745, 6126748, 6126750, 6126762, 6126756, 6126753, 6126747, 6126759, 6126744, 6126755, 6126757, 6126763, 6126764, 6126746, 6126751, 6126754, 6126760, 6126752, 6126761, 6126758, 6126749, 6126736, 6126737, 6126802, 6126804, 6126803, 6126801, 6126800, 6126778, 6126779, 6126780, 6126791, 6126799, 6126769, 6126768, 6126767, 6126766, 6126785, 6126789, 6126776, 6126777, 6126781, 6126782, 6126783, 6126784, 6126790, 6126792, 6126793, 6126794, 6126795, 6126796, 6126797, 6126798, 6126765, 6126770, 6126771, 6126772, 6126773, 6126774, 6126775, 6126809, 6126808, 6126807, 6126806, 6126805, 6126810, 6126811, 6126812, 6126813, 6126814, 6126815, 6126632, 6126818, 6126821, 6126828, 6126830, 6126836, 6126838, 6126819, 6126823, 6126826, 6126837, 6126844, 6126820, 6126842, 6126822, 6126824, 6126825, 6126832, 6126834, 6126840, 6126835, 6126841, 6126839, 6126833, 6126816, 6126817, 6127004, 6127021, 6127008, 6127011, 6127017, 6127023, 6127009, 6127005, 6127020, 6127019, 6127018, 6127016, 6127007, 6127028, 6127027, 6127026, 6127024, 6127014, 6127006, 6127061, 6127063, 6127062, 6127060, 6127044, 6127058, 6126788, 6127045, 6127043, 6127039, 6127038, 6127037, 6127036, 6127035, 6127034, 6127033, 6127032, 6127031, 6127030, 6127029, 6127040, 6127059, 6127051, 6127050, 6127049, 6127048, 6127047, 6127046, 6127073, 6127087, 6127086, 6127080, 6127078, 6127084, 6127093, 6127075, 6127074, 6127085, 6127083, 6127082, 6127079, 6127081, 6127107, 6127108, 6127064, 6127065, 6127066, 6127105, 6127106, 6127098, 6127103, 6127102, 6127101, 6127100, 6127052, 6127092, 6127095, 6127096, 6127097, 6127099, 6127041, 6127077, 6127054, 6127072, 6127071, 6127070, 6127069, 6127053, 6127104, 6127145, 6127141, 6127140, 6127147, 6127142, 6127146, 6127139, 6127134, 6127135, 6127136, 6127137, 6127138, 6126787, 6126786, 6127109, 6127042, 6127022, 6127068, 6127067, 6127076, 6127119, 6127120, 6127121, 6127122, 6127123, 6127124, 6127125, 6127126, 6127127, 6127128, 6127129, 6127130, 6127131, 6127132, 6127133, 6127110, 6127116, 6127118, 6127117, 6127160, 6127159, 6127158, 6127157, 6127156, 6127185, 6127171, 6127172, 6127173, 6127174, 6127175, 6127176, 6127177, 6127178, 6127179, 6127180, 6127181, 6127182, 6127183, 6127184, 6127186, 6127187, 6127188, 6127189, 6127190, 6127161, 6127163, 6127164, 6127165, 6127166, 6127167, 6127168, 6127169, 6127170, 6127216, 6127217, 6127218, 6127219, 6127220, 6127221, 6127207, 6127205, 6127203, 6127208, 6127209, 6127210, 6127211, 6127212, 6127213, 6127214, 6127222, 6127224, 6127227, 6127223, 6127225, 6127226, 6127230, 6127228, 6127200, 6127199, 6127198, 6127197, 6127196, 6127162, 6127229, 6127202, 6127206, 6127215, 6150515, 6150569, 6150574, 6154997, 6154999, 6157204, 6157206, 6157263, 6126742, 6127143, 6127144, 6127148, 6127115, 6127154, 6127153, 6127152, 6127151, 6127194, 6127193, 6127192, 6127191, 6127155]
    },
    'quiz.id': {
        '$in': [11776, 9486, 9232, 9491, 9500, 9506, 9003, 9391, 9011, 6837, 12086, 12089, 12090, 11512, 9410, 4933, 4934, 6866, 4955, 11505, 9080, 8700]
    },
    "quiz": { $exists: true, $ne: [] }
}, {
    '$pull': {
        'quiz': {
            'id': {
                '$in': [11776, 9486, 9232, 9491, 9500, 9506, 9003, 9391, 9011, 6837, 12086, 12089, 12090, 11512, 9410, 4933, 4934, 6866, 4955, 11505, 9080, 8700]
            }
        }
    }
}, false, true)

Firstly, it gives me an error

"errmsg" : "E11000 duplicate key error collection: test.student_quiz index: admission_id_1_quiz.id_1 dup key: { admission_id: 4500198, quiz.id: null }"

But strangely, it DOES updates one document and removes the array element, but not the other.

db.student_quiz.find({ admission_id: 4500198 })
/* 1 createdAt:12/01/2024, 14:04:08*/
{
	"_id" : ObjectId("65a0f9808e914c17aadc0aa2"),
	"admission_id" : 4500198,
	"subject_id" : 339,
	"unit_id" : 15502,
	"usm_id" : 13935,
	"acad_year" : 7,
	"quiz" : [ ],
	"school_subject_id" : 35646623,
	"shell_name" : "Chapter",
	"shell_number" : "4",
	"subject_name" : "Math",
	"unitno" : 7,
	"xp_version" : 2
},

/* 2 createdAt:12/01/2024, 12:08:20*/
{
	"_id" : ObjectId("65a0de5c8e914c17aad338f0"),
	"admission_id" : 4500198,
	"subject_id" : 333,
	"unit_id" : 17576,
	"usm_id" : 16014,
	"acad_year" : 7,
	"quiz" : [
		{
			"id" : 9410,
			"published_at" : ISODate("2024-01-12T12:12:58.021+05:30"),
			"updated_at" : ISODate("2024-01-12T12:12:58.021+05:30"),
			"complete_by" : ISODate("2024-01-17T12:12:58.021+05:30"),
			"total_questions" : "5",
			"correct" : 0,
			"wrong" : 0,
			"started_at" : null,
			"completed_at" : null
		}
	],
	"school_subject_id" : 35541934,
	"shell_name" : "Chapter",
	"shell_number" : "2",
	"subject_name" : "Chemistry",
	"unitno" : 3,
	"xp_version" : 2
}

Why is this happening? And how can i achieve my desired result?

2

Your update last few params looks weird, did you meant to pass in multi updates as an option? Without it .update will only update one document.

db.collection.update({filter}, {update}, {options})

Where {options} should be something like:

{multi:true}

It could be that the first document is updated BUT the second document update would have caused the PK violation and so it is not actually run. In this case the first update goes through and the second would not.

If you pull all the quiz items out of a document, if there an existing document that has an admission ID of 4500198 but no items in the quiz section?

I tested locally with just those two documents and it ran without an error.

Your index also looks a bit weird, quiz is an array but you’re adding an index on quiz.id, what are you trying to enforce uniqueness on?

4

Those last 2 params are just multi, and upsert. Multi is true and upsert is false. About the index, we added that index for a combination of admission_id and quiz.id, so that for every document with similar admission_id, there are unique objects in its quiz array. From what i can see, two documents with empty quiz arrays and same admission_id will not work with these indexes, right?

5

Yes, doing this twice will raise an error:

db.getCollection("Test").insert({
'admission_id':1,
'quiz':[
]
})

Something I was not sure of and tested was ordering of array items, it seems that the ordering does not matter for the index unique condition to kick in, so this causes an index violation:

db.getCollection("Test").insert({
'admission_id':3,
'quiz':[
    {
        id:3
    },
    {
        id:1
    }
]
})
db.getCollection("Test").insert({
'admission_id':3,
'quiz':[
    {
        id:1
    },
    {
        id:3
    }
]
})