-
Notifications
You must be signed in to change notification settings - Fork 0
/
mongodb_array_object.txt
731 lines (698 loc) · 165 KB
/
mongodb_array_object.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
| Natural Language Question | MongoDB Query | Collection Name |
|-------------------------|--------------|-----------------|
| What are the coordinates of the start station for each trip? | ```db.trips.distinct("start_station_location.coordinates")``` | trips |
| How many trips started from stations with coordinates within a specific geographic range? | ```db.trips.find({ "start_station_location.coordinates": { $geoWithin: { $box: [ [minLong, minLat], [maxLong, maxLat] ] } } }).count()``` | trips |
| Can you find trips where the start and end stations are the same? | ```db.trips.find({ "start_station_location": { $eq: "$end_station_location" } })``` | trips |
| What are the average latitude and longitude of the start stations? | ```db.trips.aggregate([ { $unwind: "$start_station_location.coordinates" }, { $group: { _id: null, avgLat: { $avg: { $arrayElemAt: ["$start_station_location.coordinates", 0] } }, avgLong: { $avg: { $arrayElemAt: ["$start_station_location.coordinates", 1] } } } } ])``` | trips |
| How many trips had start stations located within a certain distance from a specific point? | ```db.trips.find({ "start_station_location.coordinates": { $nearSphere: { $geometry: { type: "Point", coordinates: [longitude, latitude] }, $maxDistance: maxDistanceInMeters } } }).count()``` | trips |
| Can you find trips where the start station latitude is greater than a specific value? | ```db.trips.find({ "start_station_location.coordinates.0": { $gt: specificLatitude } })``` | trips |
| How many trips ended at stations located in a specific geographic area? | ```db.trips.find({ "end_station_location.coordinates": { $geoWithin: { $box: [ [minLong, minLat], [maxLong, maxLat] ] } } }).count()``` | trips |
| Can you find trips where the start station longitude falls within a certain range? | ```db.trips.find({ "start_station_location.coordinates.1": { $gte: minLongitude, $lte: maxLongitude } })``` | trips |
| What are the average latitude and longitude of the end stations? | ```db.trips.aggregate([ { $unwind: "$end_station_location.coordinates" }, { $group: { _id: null, avgLat: { $avg: { $arrayElemAt: ["$end_station_location.coordinates", 0] } }, avgLong: { $avg: { $arrayElemAt: ["$end_station_location.coordinates", 1] } } } } ])``` | trips |
| How many trips had end stations located within a certain distance from a specific point? | ```db.trips.find({ "end_station_location.coordinates": { $nearSphere: { $geometry: { type: "Point", coordinates: [longitude, latitude] }, $maxDistance: maxDistanceInMeters } } }).count()``` | trips |
| What are the distinct types of start station locations (e.g., latitude and longitude pairs) in the dataset? | ```db.trips.distinct("start_station_location")``` | trips |
| Can you find trips where the start station latitude is between two specific values? | ```db.trips.find({ "start_station_location.coordinates.0": { $gte: lowerLatitude, $lte: upperLatitude } })``` | trips |
| How many trips ended at stations located to the north of a specific latitude? | ```db.trips.find({ "end_station_location.coordinates.0": { $gt: specificLatitude } }).count()``` | trips |
| What are the unique combinations of latitude and longitude for start stations? | ```db.trips.aggregate([ { $group: { _id: { latitude: { $arrayElemAt: ["$start_station_location.coordinates", 0] }, longitude: { $arrayElemAt: ["$start_station_location.coordinates", 1] } } } } ])``` | trips |
| Can you find trips where the start station longitude is less than a specific value? | ```db.trips.find({ "start_station_location.coordinates.1": { $lt: specificLongitude } })``` | trips |
| How many trips ended at stations located in a specific city or region? | ```db.trips.find({ "end_station_location.coordinates": { $geoWithin: { $geometry: { type: "Polygon", coordinates: [[ [long1, lat1], [long2, lat2], ...]] } } } }).count()``` | trips |
| Can you find trips where the start station coordinates match a specific pair of latitude and longitude? | ```db.trips.find({ "start_station_location.coordinates": [specificLatitude, specificLongitude] })``` | trips |
| How many trips started at stations located to the west of a specific longitude? | ```db.trips.find({ "start_station_location.coordinates.1": { $lt: specificLongitude } }).count()``` | trips |
| What are the distinct types of end station locations (e.g., latitude and longitude pairs) in the dataset? | ```db.trips.distinct("end_station_location")``` | trips |
| Can you find trips where the end station latitude falls within a certain range? | ```db.trips.find({ "end_station_location.coordinates.0": { $gte: minLatitude, $lte: maxLatitude } })``` | trips |
| How many trips ended at stations located at a specific latitude and longitude? | ```db.trips.find({ "end_station_location.coordinates": [specificLatitude, specificLongitude] }).count()``` | trips |
| Can you find trips where the start station longitude is outside a specific range? | ```db.trips.find({ $or: [ { "start_station_location.coordinates.1": { $lt: minLongitude } }, { "start_station_location.coordinates.1": { $gt: maxLongitude } } ] })``` | trips |
| How many trips started at stations located to the south of a specific latitude? | ```db.trips.find({ "start_station_location.coordinates.0": { $lt: specificLatitude } }).count()``` | trips |
| Can you find companies with a specific product name in their product list? | ```db.companies.find({ "products.name": "specific_product_name" })``` | companies |
| What are the names and URLs of products offered by each company? | ```db.companies.aggregate([ { $unwind: "$products" }, { $project: { _id: 0, company_name: "$name", product_name: "$products.name", product_permalink: "$products.permalink" } } ])``` | companies |
| How many companies have a specific person as part of their relationships list? | ```db.companies.find({ "relationships.person.permalink": "specific_person_permalink" }).count()``` | companies |
| Can you find companies with competitors having specific names in their competitions list? | ```db.companies.find({ "competitions.competitor.name": "specific_competitor_name" })``` | companies |
| What are the addresses of offices for each company? | ```db.companies.aggregate([ { $unwind: "$offices" }, { $project: { _id: 0, company_name: "$name", office_description: "$offices.description", address1: "$offices.address1", address2: "$offices.address2", city: "$offices.city", state_code: "$offices.state_code", country_code: "$offices.country_code", zip_code: "$offices.zip_code" } } ])``` | companies |
| What are the names of competitors for each company? | ```db.companies.aggregate([ { $unwind: "$competitions" }, { $project: { _id: 0, company_name: "$name", competitor_name: "$competitions.competitor.name" } } ])``` | companies |
| What are the names and URLs of the blogs for each company? | ```db.companies.find({ "blog_url": { $exists: true } }, { "name": 1, "blog_url": 1 })``` | companies |
| Can you find companies with specific keywords in their tag list? | ```db.companies.find({ "tag_list": { $regex: /specific_keyword/i } })``` | companies |
| What are the names of people in relationships with each company, along with their titles? | ```db.companies.aggregate([ { $unwind: "$relationships" }, { $project: { _id: 0, company_name: "$name", person_name: "$relationships.person.first_name", person_last_name: "$relationships.person.last_name", title: "$relationships.title" } } ])``` | companies |
| Find all students who have a score greater than 90 in any type of assessment. | `db.grades.find({"scores.score": {$gt: 90}})` | grades |
| Get the student IDs of students who have scored less than 60 in the "quiz" type of assessment. | `db.grades.find({"scores.type": "quiz", "scores.score": {$lt: 60}}, {student_id: 1, _id: 0})` | grades |
| Find the average score for each student in the "exam" type of assessment. | `db.grades.aggregate([{$unwind: "$scores"}, {$match: {"scores.type": "exam"}}, {$group: {_id: "$student_id", avg_exam_score: {$avg: "$scores.score"}}}])` | grades |
| Get the class IDs of classes where at least one student has scored above 95 in the "homework" type of assessment. | `db.grades.distinct("class_id", {"scores.type": "homework", "scores.score": {$gt: 95}})` | grades |
| Find the maximum score for each type of assessment across all students. | `db.grades.aggregate([{$unwind: "$scores"}, {$group: {_id: "$scores.type", max_score: {$max: "$scores.score"}}}])` | grades |
| Get the student IDs of students who have not taken any "quiz" type of assessment. | `db.grades.distinct("student_id", {scores: {$not: {$elemMatch: {type: "quiz"}}}})` | grades |
| Find the total number of scores for each type of assessment. | `db.grades.aggregate([{$unwind: "$scores"}, {$group: {_id: "$scores.type", total_scores: {$sum: 1}}}])` | grades |
| Get the class IDs of classes where the average score for the "exam" type of assessment is greater than 80. | `db.grades.aggregate([{$unwind: "$scores"}, {$match: {"scores.type": "exam"}}, {$group: {_id: "$class_id", avg_exam_score: {$avg: "$scores.score"}}}, {$match: {avg_exam_score: {$gt: 80}}}, {$group: {_id: null, class_ids: {$push: "$_id"}}}])` | grades |
| Get the class IDs of classes where all students have scored above 70 in the "exam" type of assessment. | `db.grades.aggregate([{$match: {"scores.type": "exam", "scores.score": {$gt: 70}}}, {$group: {_id: "$class_id", student_ids: {$addToSet: "$student_id"}}}, {$project: {class_id: "$_id", _id: 0, num_students: {$size: "$student_ids"}}}, {$group: {_id: null, qualified_classes: {$push: {class_id: "$class_id", num_students: "$num_students"}}}}, {$unwind: "$qualified_classes"}, {$replaceRoot: {newRoot: "$qualified_classes"}}, {$match: {num_students: {$gt: 0}}}])` | grades |
| Find the student IDs of students who have not taken any assessment. | `db.grades.distinct("student_id", {scores: {$size: 0}})` | grades |
| Get the class IDs of classes where the difference between the maximum and minimum scores in the "homework" type of assessment is greater than 20. | `db.grades.aggregate([{$unwind: "$scores"}, {$match: {"scores.type": "homework"}}, {$group: {_id: "$class_id", max_score: {$max: "$scores.score"}, min_score: {$min: "$scores.score"}}}, {$match: {$expr: {$gt: [{$subtract: ["$max_score", "$min_score"]}, 20]}}}, {$group: {_id: null, class_ids: {$push: "$_id"}}}])` | grades |
| Find the student IDs of students who have scored more than 80 in all types of assessments. | `db.grades.aggregate([{$unwind: "$scores"}, {$group: {_id: {student_id: "$student_id", type: "$scores.type"}, score: {$push: "$scores.score"}}}, {$group: {_id: "$_id.student_id", scores: {$push: {type: "$_id.type", scores: "$score"}}}}, {$match: {"scores.scores": {$not: {$elemMatch: {$lte: 80}}}}}, {$project: {_id: 1}}])` | grades |
| Get the class IDs of classes where the average score for the "quiz" type of assessment is less than 60. | `db.grades.aggregate([{$unwind: "$scores"}, {$match: {"scores.type": "quiz"}}, {$group: {_id: "$class_id", avg_quiz_score: {$avg: "$scores.score"}}}, {$match: {avg_quiz_score: {$lt: 60}}}, {$group: {_id: null, class_ids: {$push: "$_id"}}}])` | grades |
| Find the student IDs of students who have scored the same in all types of assessments. | `db.grades.aggregate([{$unwind: "$scores"}, {$group: {_id: {student_id: "$student_id", score: "$scores.score"}}}, {$group: {_id: "$_id.student_id", scores: {$addToSet: "$_id.score"}}}, {$match: {scores: {$size: 1}}}, {$project: {_id: 1}}])` | grades |
| Get the class IDs of classes where the difference between the maximum and minimum scores in the "exam" type of assessment is less than 10. | `db.grades.aggregate([{$unwind: "$scores"}, {$match: {"scores.type": "exam"}}, {$group: {_id: "$class_id", max_score: {$max: "$scores.score"}, min_score: {$min: "$scores.score"}}}, {$match: {$expr: {$lt: [{$subtract: ["$max_score", "$min_score"]}, 10]}}}, {$group: {_id: null, class_ids: {$push: "$_id"}}}])` | grades |
| Find the student IDs of students who have scored more than 90 in at least two types of assessments. | `db.grades.aggregate([{$unwind: "$scores"}, {$group: {_id: {student_id: "$student_id", type: "$scores.type"}, score: {$push: "$scores.score"}}}, {$group: {_id: "$_id.student_id", scores: {$push: {type: "$_id.type", scores: "$score"}}}}, {$match: {"scores.scores": {$elemMatch: {$gt: 90}}}}, {$group: {_id: "$_id", num_high_scores: {$sum: {$cond: [{$gt: ["$scores.scores", [90]]}, 1, 0]}}}}, {$match: {num_high_scores: {$gt: 1}}}, {$project: {_id: 1}}])` | grades |
| Get the class IDs of classes where the standard deviation of scores in the "homework" type of assessment is greater than 10. | `db.grades.aggregate([{$unwind: "$scores"}, {$match: {"scores.type": "homework"}}, {$group: {_id: "$class_id", scores: {$push: "$scores.score"}}}, {$addFields: {std_dev: {$stdDevPop: "$scores"}}}, {$match: {std_dev: {$gt: 10}}}, {$group: {_id: null, class_ids: {$push: "$_id"}}}])` | grades |
| Find the student IDs of students who have scored between 70 and 80 in the "quiz" type of assessment. | `db.grades.find({"scores.type": "quiz", "scores.score": {$gte: 70, $lte: 80}}, {student_id: 1, _id: 0})` | grades |
| Get the class IDs of classes where the maximum score for the "homework" type of assessment is less than 90. | `db.grades.aggregate([{$unwind: "$scores"}, {$match: {"scores.type": "homework"}}, {$group: {_id: "$class_id", max_score: {$max: "$scores.score"}}}, {$match: {max_score: {$lt: 90}}}, {$group: {_id: null, class_ids: {$push: "$_id"}}}])` | grades |
| Find the student IDs of students who have scored above 80 in all types of assessments except the "exam" type. | `db.grades.aggregate([{$unwind: "$scores"}, {$group: {_id: {student_id: "$student_id", type: "$scores.type"}, score: {$push: "$scores.score"}}}, {$group: {_id: "$_id.student_id", scores: {$push: {type: "$_id.type", scores: "$score"}}}}, {$match: {"scores.scores": {$not: {$elemMatch: {$lte: 80}}}, "scores.type": {$ne: "exam"}}}, {$project: {_id: 1}}])` | grades |
| Get the class IDs of classes where the average score for the "homework" type of assessment is between 70 and 80. | `db.grades.aggregate([{$unwind: "$scores"}, {$match: {"scores.type": "homework"}}, {$group: {_id: "$class_id", avg_homework_score: {$avg: "$scores.score"}}}, {$match: {avg_homework_score: {$gte: 70, $lte: 80}}}, {$group: {_id: null, class_ids: {$push: "$_id"}}}])` | grades |
| Find the student IDs of students who have scored the same in all types of assessments and that score is greater than 85. | `db.grades.aggregate([{$unwind: "$scores"}, {$group: {_id: {student_id: "$student_id", score: "$scores.score"}}}, {$group: {_id: "$_id.student_id", scores: {$addToSet: "$_id.score"}}}, {$match: {scores: {$size: 1}, scores: {$elemMatch: {$gt: 85}}}}, {$project: {_id: 1}}])` | grades |
| Get the class IDs of classes where the difference between the maximum and minimum scores in the "quiz" type of assessment is exactly 20. | `db.grades.aggregate([{$unwind: "$scores"}, {$match: {"scores.type": "quiz"}}, {$group: {_id: "$class_id", max_score: {$max: "$scores.score"}, min_score: {$min: "$scores.score"}}}, {$match: {$expr: {$eq: [{$subtract: ["$max_score", "$min_score"]}, 20]}}}, {$group: {_id: null, class_ids: {$push: "$_id"}}}])` | grades |
| Find the student IDs of students who have scored more than 90 in at least one type of assessment and less than 60 in at least one other type of assessment. | `db.grades.aggregate([{$unwind: "$scores"}, {$group: {_id: {student_id: "$student_id", type: "$scores.type"}, score: {$push: "$scores.score"}}}, {$group: {_id: "$_id.student_id", scores: {$push: {type: "$_id.type", scores: "$score"}}}}, {$match: {$and: [{"scores.scores": {$elemMatch: {$gt: 90}}}, {"scores.scores": {$elemMatch: {$lt: 60}}}]}}, {$project: {_id: 1}}])` | grades |
| Get the class IDs of classes where the median score for the "exam" type of assessment is greater than 80. | `db.grades.aggregate([{$unwind: "$scores"}, {$match: {"scores.type": "exam"}}, {$group: {_id: "$class_id", exam_scores: {$push: "$scores.score"}}}, {$addFields: {median_score: {$arrayElemAt: [{$sortArray: {input: "$exam_scores", sortBy: 1}}, {$ceil: {$divide: [{$size: "$exam_scores"}, 2]} - 1}]}}}, {$match: {median_score: {$gt: 80}}}, {$group: {_id: null, class_ids: {$push: "$_id"}}}])` | grades |
| Find the student IDs of students who have not taken any assessment in a particular class (class ID provided). | `db.grades.distinct("student_id", {class_id: <class_id>, scores: {$size: 0}})` | grades |
| Get the class IDs of classes where the range (difference between maximum and minimum scores) for the "homework" type of assessment is less than 15. | `db.grades.aggregate([{$unwind: "$scores"}, {$match: {"scores.type": "homework"}}, {$group: {_id: "$class_id", max_score: {$max: "$scores.score"}, min_score: {$min: "$scores.score"}}}, {$addFields: {range: {$subtract: ["$max_score", "$min_score"]}}}, {$match: {range: {$lt: 15}}}, {$group: {_id: null, class_ids: {$push: "$_id"}}}])` | grades |
| Find the student IDs of students who have scored more than 90 in the "exam" type of assessment and less than 70 in the "quiz" type of assessment. | `db.grades.aggregate([{$unwind: "$scores"}, {$group: {_id: {student_id: "$student_id", type: "$scores.type"}, score: {$push: "$scores.score"}}}, {$group: {_id: "$_id.student_id", scores: {$push: {type: "$_id.type", scores: "$score"}}}}, {$match: {$and: [{"scores.scores": {$elemMatch: {$gt: 90, $elemMatch: {$eq: "exam"}}}}, {"scores.scores": {$elemMatch: {$lt: 70, $elemMatch: {$eq: "quiz"}}}}]}}, {$project: {_id: 1}}])` | grades |
| Get the class IDs of classes where the difference between the average scores of the "exam" and "homework" types of assessments is less than 5. | `db.grades.aggregate([{$unwind: "$scores"}, {$group: {_id: {class_id: "$class_id", type: "$scores.type"}, avg_score: {$avg: "$scores.score"}}}, {$group: {_id: "$_id.class_id", exam_avg: {$avg: {$cond: [{$eq: ["$_id.type", "exam"]}, "$avg_score", 0]}}, homework_avg: {$avg: {$cond: [{$eq: ["$_id.type", "homework"]}, "$avg_score", 0]}}}}, {$addFields: {diff: {$abs: {$subtract: ["$exam_avg", "$homework_avg"]}}}}, {$match: {diff: {$lt: 5}}}, {$group: {_id: null, class_ids: {$push: "$_id"}}}])` | grades |
| Find the student IDs of students who have scored more than 80 in all types of assessments and the difference between their maximum and minimum scores is less than 10. | `db.grades.aggregate([{$unwind: "$scores"}, {$group: {_id: {student_id: "$student_id", type: "$scores.type"}, score: {$push: "$scores.score"}}}, {$group: {_id: "$_id.student_id", scores: {$push: {type: "$_id.type", scores: "$score"}}}}, {$match: {"scores.scores": {$not: {$elemMatch: {$lte: 80}}}}}, {$addFields: {max_score: {$max: "$scores.scores"}, min_score: {$min: "$scores.scores"}}}, {$match: {$expr: {$lt: [{$subtract: ["$max_score", "$min_score"]}, 10]}}}, {$project: {_id: 1}}])` | grades |
| Get the class IDs of classes where the standard deviation of scores in the "exam" type of assessment is less than 5. | `db.grades.aggregate([{$unwind: "$scores"}, {$match: {"scores.type": "exam"}}, {$group: {_id: "$class_id", scores: {$push: "$scores.score"}}}, {$addFields: {std_dev: {$stdDevPop: "$scores"}}}, {$match: {std_dev: {$lt: 5}}}, {$group: {_id: null, class_ids: {$push: "$_id"}}}])` | grades |
| Find the student IDs of students who have scored between 70 and 80 in all types of assessments. | `db.grades.aggregate([{$unwind: "$scores"}, {$group: {_id: {student_id: "$student_id", type: "$scores.type"}, score: {$push: "$scores.score"}}}, {$group: {_id: "$_id.student_id", scores: {$push: {type: "$_id.type", scores: "$score"}}}}, {$match: {"scores.scores": {$not: {$elemMatch: {$lt: 70, $gt: 80}}}}}, {$project: {_id: 1}}])` | grades |
| Get the class IDs of classes where the maximum score for the "exam" type of assessment is greater than 95 and the minimum score is less than 60. | `db.grades.aggregate([{$unwind: "$scores"}, {$match: {"scores.type": "exam"}}, {$group: {_id: "$class_id", max_score: {$max: "$scores.score"}, min_score: {$min: "$scores.score"}}}, {$match: {$and: [{max_score: {$gt: 95}}, {min_score: {$lt: 60}}]}}, {$group: {_id: null, class_ids: {$push: "$_id"}}}])` | grades |
| Find the student IDs of students who have scored more than 90 in the "quiz" type of assessment and less than 70 in the "homework" type of assessment. | `db.grades.aggregate([{$unwind: "$scores"}, {$group: {_id: {student_id: "$student_id", type: "$scores.type"}, score: {$push: "$scores.score"}}}, {$group: {_id: "$_id.student_id", scores: {$push: {type: "$_id.type", scores: "$score"}}}}, {$match: {$and: [{"scores.scores": {$elemMatch: {$gt: 90, $elemMatch: {$eq: "quiz"}}}}, {"scores.scores": {$elemMatch: {$lt: 70, $elemMatch: {$eq: "homework"}}}}]}}, {$project: {_id: 1}}])` | grades |
| Get the class IDs of classes where the average score for the "quiz" type of assessment is greater than 80 and the average score for the "homework" type of assessment is less than 70. | `db.grades.aggregate([{$unwind: "$scores"}, {$group: {_id: {class_id: "$class_id", type: "$scores.type"}, avg_score: {$avg: "$scores.score"}}}, {$group: {_id: "$_id.class_id", quiz_avg: {$avg: {$cond: [{$eq: ["$_id.type", "quiz"]}, "$avg_score", 0]}}, homework_avg: {$avg: {$cond: [{$eq: ["$_id.type", "homework"]}, "$avg_score", 0]}}}}, {$match: {$and: [{quiz_avg: {$gt: 80}}, {homework_avg: {$lt: 70}}]}}, {$group: {_id: null, class_ids: {$push: "$_id"}}}])` | grades |
| Find the student IDs of students who have scored the same in all types of assessments and that score is between 70 and 80. | `db.grades.aggregate([{$unwind: "$scores"}, {$group: {_id: {student_id: "$student_id", score: "$scores.score"}}}, {$group: {_id: "$_id.student_id", scores: {$addToSet: "$_id.score"}}}, {$match: {scores: {$size: 1, $elemMatch: {$gte: 70, $lte: 80}}}}, {$project: {_id: 1}}])` | grades |
| Get the class IDs of classes where the difference between the maximum and minimum scores in the "quiz" type of assessment is greater than 30. | `db.grades.aggregate([{$unwind: "$scores"}, {$match: {"scores.type": "quiz"}}, {$group: {_id: "$class_id", max_score: {$max: "$scores.score"}, min_score: {$min: "$scores.score"}}}, {$match: {$expr: {$gt: [{$subtract: ["$max_score", "$min_score"]}, 30]}}}, {$group: {_id: null, class_ids: {$push: "$_id"}}}])` | grades |
| Find the student IDs of students who have scored more than 90 in at least two types of assessments and less than 60 in at least one other type of assessment. | `db.grades.aggregate([{$unwind: "$scores"}, {$group: {_id: {student_id: "$student_id", type: "$scores.type"}, score: {$push: "$scores.score"}}}, {$group: {_id: "$_id.student_id", scores: {$push: {type: "$_id.type", scores: "$score"}}}}, {$match: {$and: [{"scores.scores": {$elemMatch: {$gt: 90}}}, {"scores.scores": {$elemMatch: {$lt: 60}}}, {"scores.scores": {$elemMatch: {$gt: 90, $size: 2}}}]}}, {$project: {_id: 1}}])` | grades |
| Get the class IDs of classes where the median score for the "homework" type of assessment is between 75 and 85. | `db.grades.aggregate([{$unwind: "$scores"}, {$match: {"scores.type": "homework"}}, {$group: {_id: "$class_id", homework_scores: {$push: "$scores.score"}}}, {$addFields: {median_score: {$arrayElemAt: [{$sortArray: {input: "$homework_scores", sortBy: 1}}, {$ceil: {$divide: [{$size: "$homework_scores"}, 2]} - 1}]}}}, {$match: {median_score: {$gte: 75, $lte: 85}}}, {$group: {_id: null, class_ids: {$push: "$_id"}}}])` | grades |
| Find the student IDs of students who have not taken any assessment in any class. | `db.grades.distinct("student_id", {scores: {$size: 0}})` | grades |
| What is the maximum wind speed recorded? | `db.weather.find({}, {"wind.speed.rate": 1}).sort({"wind.speed.rate": -1}).limit(1)` | weather |
| Find all stations with a visibility distance greater than 10 miles. | `db.weather.find({"visibility.distance.value": {$gt: 10}})` | weather |
| Get the count of records where the air temperature quality is "good". | `db.weather.count({"airTemperature.quality": "good"})` | weather |
| Find all records where the wind direction angle is between 180 and 360 degrees. | `db.weather.find({"wind.direction.angle": {$gte: 180, $lte: 360}})` | weather |
| Get the average dew point value. | `db.weather.aggregate([{$group: {_id: null, avgDewPoint: {$avg: "$dewPoint.value"}}}])` | weather |
| Find all stations with ceiling height determination "NOINSTRUMENT". | `db.weather.find({"skyCondition.ceilingHeight.determination": "NOINSTRUMENT"})` | weather |
| Get the count of records where the precipitation estimated observation has a discrepancy. | `db.weather.count({"precipitationEstimatedObservation.discrepancy": {$exists: true}})` | weather |
| Find all records with past weather observation manual atmospheric condition "DUST". | `db.weather.find({"pastWeatherObservationManual.atmosphericCondition.value": "DUST"})` | weather |
| Get the distinct values of quality control processes. | `db.weather.distinct("qualityControlProcess")` | weather |
| Find all records where the pressure value is between 990 and 1010 hPa. | `db.weather.find({"pressure.value": {$gte: 990, $lte: 1010}})` | weather |
| Get the count of records with more than one section. | `db.weather.count({sections: {$size: {$gt: 1}}})` | weather |
| Find all records where the variability value is "STEADY". | `db.weather.find({"variability.value": "STEADY"})` | weather |
| Get the maximum ceiling height value. | `db.weather.find({}, {"skyCondition.ceilingHeight.value": 1}).sort({"skyCondition.ceilingHeight.value": -1}).limit(1)` | weather |
| Find all records where the past weather observation manual period is greater than 6 hours. | `db.weather.find({"pastWeatherObservationManual.period.value": {$gt: 6}})` | weather |
| Get the distinct values of data sources. | `db.weather.distinct("dataSource")` | weather |
| Find all records where the position type is "Point". | `db.weather.find({"position.type": "Point"})` | weather |
| Get the count of records with a valid air temperature value (not null). | `db.weather.count({"airTemperature.value": {$ne: null}})` | weather |
| Find all records where the wind speed quality is "good" and the wind direction angle is between 270 and 360 degrees. | `db.weather.find({"wind.speed.quality": "good", "wind.direction.angle": {$gte: 270, $lte: 360}})` | weather |
| Get the distinct values of station identifiers. | `db.weather.distinct("st")` | weather |
| Find all records where the visibility distance quality is "good" and the visibility distance value is greater than 5 miles. | `db.weather.find({"visibility.distance.quality": "good", "visibility.distance.value": {$gt: 5}})` | weather |
| Find all records where the air temperature value is below freezing (0°C). | `db.weather.find({"airTemperature.value": {$lt: 0}})` | weather |
| Get the distinct values of ceiling height determination. | `db.weather.distinct("skyCondition.ceilingHeight.determination")` | weather |
| Find all records where the pressure quality is "good" and the pressure value is greater than 1020 hPa. | `db.weather.find({"pressure.quality": "good", "pressure.value": {$gt: 1020}})` | weather |
| Get the count of records where the wind direction quality is "good". | `db.weather.count({"wind.direction.quality": "good"})` | weather |
| Find all records where the dew point value is within 2 degrees of the air temperature value. | `db.weather.find({$where: "Math.abs(this.dewPoint.value - this.airTemperature.value) <= 2"})` | weather |
| Get the distinct values of atmospheric conditions in past weather observation manual. | `db.weather.distinct("pastWeatherObservationManual.atmosphericCondition.value")` | weather |
| Find all records where the visibility distance value is less than 1 mile. | `db.weather.find({"visibility.distance.value": {$lt: 1}})` | weather |
| Get the average wind speed rate. | `db.weather.aggregate([{$group: {_id: null, avgWindSpeed: {$avg: "$wind.speed.rate"}}}])` | weather |
| Find all records where the variability quality is "good". | `db.weather.find({"variability.quality": "good"})` | weather |
| Get the count of records where the ceiling height value is missing (null). | `db.weather.count({"skyCondition.ceilingHeight.value": null})` | weather |
| Find all records where the station identifier starts with "K". | `db.weather.find({st: /^K/})` | weather |
| Find all records where the estimated water depth is greater than 0.5 inches. | `db.weather.find({"precipitationEstimatedObservation.estimatedWaterDepth": {$gt: 0.5}})` | weather |
| Get the count of records where the ceiling height cavok is "YES". | `db.weather.count({"skyCondition.ceilingHeight.cavok": "YES"})` | weather |
| Find all records where the position elevation is greater than 1000 feet. | `db.weather.find({"position.elevation": {$gt: 1000}})` | weather |
| Find all records where the past weather observation manual period quality is "good". | `db.weather.find({"pastWeatherObservationManual.period.quality": "good"})` | weather |
| Get the count of records where the wind direction angle is between 0 and 90 degrees. | `db.weather.count({"wind.direction.angle": {$gte: 0, $lte: 90}})` | weather |
| Get the distinct values of position types. | `db.weather.distinct("position.type")` | weather |
| Find restaurants in the Bronx borough. | `db.restaurants.find({ 'address.borough': 'Bronx' })` | restaurants |
| Find restaurants on Main St street. | `db.restaurants.find({ 'address.street': 'Main St' })` | restaurants |
| Find restaurants with zipcode 10001. | `db.restaurants.find({ 'address.zipcode': '10001' })` | restaurants |
| Find restaurants at coordinate [-73.9, 40.7]. | `db.restaurants.find({ 'address.coord': [ -73.9, 40.7 ] })` | restaurants |
| Find restaurants with grade score greater than 20. | `db.restaurants.find({ 'grades.score': { $gt: 20 } })` | restaurants |
| Find restaurants with grade date on or after 2023-01-01. | `db.restaurants.find({ 'grades.date': { $gte: ISODate('2023-01-01') } })` | restaurants |
| Find restaurants with grade 'A'. | `db.restaurants.find({ 'grades.grade': 'A' })` | restaurants |
| Find restaurants with building number 123. | `db.restaurants.find({ 'address.building': '123' })` | restaurants |
| Find restaurants with multiple grades. | `db.restaurants.find({ 'grades.1': { $exists: true } })` | restaurants |
| Find restaurants with grade 'B' and score between 20 and 30. | `db.restaurants.find({ 'grades.grade': 'B', 'grades.score': { $gte: 20, $lte: 30 } })` | restaurants |
| Find restaurants on Main St street with Mexican cuisine. | `db.restaurants.find({ 'address.street': 'Main St', cuisine: 'Mexican' })` | restaurants |
| Find restaurants with Chinese cuisine in Manhattan borough. | `db.restaurants.find({ cuisine: 'Chinese', 'address.borough': 'Manhattan' })` | restaurants |
| Find restaurants named 'Sushi Bar' at coordinate [-74.0, 40.7]. | `db.restaurants.find({ name: 'Sushi Bar', 'address.coord': [ -74.0, 40.7 ] })` | restaurants |
| Find restaurants with grade 'A' and building number 123. | `db.restaurants.find({ 'grades.grade': 'A', 'address.building': '123' })` | restaurants |
| Find restaurants with Thai cuisine and zipcode 10002. | `db.restaurants.find({ cuisine: 'Thai', 'address.zipcode': '10002' })` | restaurants |
| Find restaurants with grade 'B' on Broadway street. | `db.restaurants.find({ 'grades.grade': 'B', 'address.street': 'Broadway' })` | restaurants |
| Find restaurants with Indian cuisine and multiple grades. | `db.restaurants.find({ cuisine: 'Indian', 'grades.1': { $exists: true } })` | restaurants |
| Find restaurants named 'Steakhouse' with grade score between 25 and 30. | `db.restaurants.find({ name: 'Steakhouse', 'grades.score': { $gte: 25, $lte: 30 } })` | restaurants |
| Find restaurants in Queens borough with grade date in 2022. | `db.restaurants.find({ 'address.borough': 'Queens', 'grades.date': { $gte: ISODate('2022-01-01'), $lte: ISODate('2022-12-31') } })` | restaurants |
| Find restaurants with French cuisine and building number 456. | `db.restaurants.find({ cuisine: 'French', 'address.building': '456' })` | restaurants |
| Find restaurants named 'Cafe' with multiple grades and score 25. | `db.restaurants.find({ name: 'Cafe', 'grades.score': 25, 'grades.1': { $exists: true } })` | restaurants |
| Find restaurants at coordinate [-73.9, 40.8] with grade date on or after 2023-01-01. | `db.restaurants.find({ 'address.coord': [ -73.9, 40.8 ], 'grades.date': { $gte: ISODate('2023-01-01') } })` | restaurants |
| Find restaurants with Japanese cuisine and grade score between 20 and 30. | `db.restaurants.find({ cuisine: 'Japanese', 'grades.score': { $gte: 20, $lte: 30 } })` | restaurants |
| Find restaurants named 'Pizzeria' in Brooklyn borough. | `db.restaurants.find({ 'address.borough': 'Brooklyn', name: 'Pizzeria' })` | restaurants |
| Find restaurants with zipcode 10003 and grade 'A'. | `db.restaurants.find({ 'address.zipcode': '10003', 'grades.grade': 'A' })` | restaurants |
| Find restaurants on Park Ave street and having grade score of 28. | `db.restaurants.find({ 'address.street': 'Park Ave', 'grades.score': 28 })` | restaurants |
| Find restaurants with grade score of 10. | `db.restaurants.find({ 'grades.score': 10 })` | restaurants |
| Find restaurants with no grades. | `db.restaurants.find({ grades: { $size: 0 } })` | restaurants |
| Find restaurants with cuisine 'American' and borough 'Staten Island'. | `db.restaurants.find({ cuisine: 'American', 'address.borough': 'Staten Island' })` | restaurants |
| Find restaurants with name containing 'Pizza'. | `db.restaurants.find({ name: { $regex: /Pizza/i } })` | restaurants |
| Find restaurants with building number '1' and zipcode '10001'. | `db.restaurants.find({ 'address.building': '1', 'address.zipcode': '10001' })` | restaurants |
| Find restaurants with grade date in March 2023. | `db.restaurants.find({ 'grades.date': { $gte: ISODate('2023-03-01'), $lte: ISODate('2023-03-31') } })` | restaurants |
| Find restaurants with no address coordinate. | `db.restaurants.find({ 'address.coord': { $exists: false } })` | restaurants |
| Find restaurants with grade 'A' or 'B'. | `db.restaurants.find({ 'grades.grade': { $in: ['A', 'B'] } })` | restaurants |
| Find restaurants with name starting with 'Mc'. | `db.restaurants.find({ name: { $regex: /^Mc/ } })` | restaurants |
| Find restaurants with cuisine 'Spanish' and borough 'Bronx'. | `db.restaurants.find({ cuisine: 'Spanish', 'address.borough': 'Bronx' })` | restaurants |
| Find restaurants with grade score less than 15. | `db.restaurants.find({ 'grades.score': { $lt: 15 } })` | restaurants |
| Find restaurants with no street name. | `db.restaurants.find({ 'address.street': { $exists: false } })` | restaurants |
| Find restaurants with name ending with 'Cafe'. | `db.restaurants.find({ name: { $regex: /Cafe$/ } })` | restaurants |
| Find restaurants with cuisine 'Greek' and zipcode '11101'. | `db.restaurants.find({ cuisine: 'Greek', 'address.zipcode': '11101' })` | restaurants |
| Find restaurants with grade 'C' or no grade. | `db.restaurants.find({ $or: [{ 'grades.grade': 'C' }, { grades: { $size: 0 } }] })` | restaurants |
| Find restaurants with building number '100' and borough 'Queens'. | `db.restaurants.find({ 'address.building': '100', 'address.borough': 'Queens' })` | restaurants |
| Find restaurants with grade date in February 2022. | `db.restaurants.find({ 'grades.date': { $gte: ISODate('2022-02-01'), $lte: ISODate('2022-02-28') } })` | restaurants |
| Find restaurants with name containing 'Deli'. | `db.restaurants.find({ name: { $regex: /Deli/i } })` | restaurants |
| Find restaurants with cuisine 'Vietnamese' and borough 'Manhattan'. | `db.restaurants.find({ cuisine: 'Vietnamese', 'address.borough': 'Manhattan' })` | restaurants |
| Find restaurants with grade score greater than 25 and less than 30. | `db.restaurants.find({ 'grades.score': { $gt: 25, $lt: 30 } })` | restaurants |
| Find restaurants with no building number. | `db.restaurants.find({ 'address.building': { $exists: false } })` | restaurants |
| Find restaurants with name starting with 'The '. | `db.restaurants.find({ name: { $regex: /^The / } })` | restaurants |
| Find restaurants with cuisine 'Turkish' and borough 'Brooklyn'. | `db.restaurants.find({ cuisine: 'Turkish', 'address.borough': 'Brooklyn' })` | restaurants |
| Find restaurants with grade score of 20 or 25. | `db.restaurants.find({ 'grades.score': { $in: [20, 25] } })` | restaurants |
| Find restaurants with no zipcode. | `db.restaurants.find({ 'address.zipcode': { $exists: false } })` | restaurants |
| Find restaurants with name containing 'Bakery'. | `db.restaurants.find({ name: { $regex: /Bakery/i } })` | restaurants |
| Find restaurants with cuisine 'Indian' and borough 'Queens'. | `db.restaurants.find({ cuisine: 'Indian', 'address.borough': 'Queens' })` | restaurants |
| Find restaurants with grade 'B' and score greater than or equal to 20. | `db.restaurants.find({ 'grades.grade': 'B', 'grades.score': { $gte: 20 } })` | restaurants |
| Find restaurants with building number '200' and borough 'Bronx'. | `db.restaurants.find({ 'address.building': '200', 'address.borough': 'Bronx' })` | restaurants |
| Find restaurants with grade date in December 2021. | `db.restaurants.find({ 'grades.date': { $gte: ISODate('2021-12-01'), $lte: ISODate('2021-12-31') } })` | restaurants |
| Find all movies with a Rotten Tomatoes critics rating greater than 90%. | `db.movies.find({"tomatoes.critic.rating": {$gt: 9}})` | movies |
| Get the titles of movies with an IMDB rating greater than 8.5. | `db.movies.find({"imdb.rating": {$gt: 8.5}}, {title: 1, _id: 0})` | movies |
| Get the movie titles and plots for movies with more than 1000 IMDB votes. | `db.movies.find({"imdb.votes": {$gt: 1000}}, {title: 1, plot: 1, _id: 0})` | movies |
| Find all movies with at least one writer in the "writers" array. | `db.movies.find({writers: {$exists: true, $ne: []}})` | movies |
| Find all movies with a Rotten Tomatoes viewer rating greater than 4 and sort them by title. | `db.movies.find({"tomatoes.viewer.rating": {$gt: 4}}).sort({title: 1})` | movies |
| Count the number of movies in each genre. | `db.movies.aggregate([{$unwind: "$genres"}, {$group: {_id: "$genres", count: {$sum: 1}}}])` | movies |
| Find all movies with the word "Adventure" in the plot. | `db.movies.find({plot: /Adventure/i})` | movies |
| Get the movie titles and IMDB ratings for movies with more than 10 awards wins. | `db.movies.find({"awards.wins": {$gt: 10}}, {title: 1, "imdb.rating": 1, _id: 0})` | movies |
| Find all movies with at least one director in common with the movie "Inception". | `db.movies.find({directors: {$in: (db.movies.findOne({title: "Inception"}).directors)}})` | movies |
| Get the movie titles and genres for movies with a Rotten Tomatoes critics rating greater than 90% and an IMDB rating greater than 8.5. | `db.movies.find({"tomatoes.critic.rating": {$gt: 9}, "imdb.rating": {$gt: 8.5}}, {title: 1, genres: 1, _id: 0})` | movies |
| Count the number of movies with more than 5 languages. | `db.movies.count({languages: {$size: {$gt: 5}}})` | movies |
| Find all movies with a Rotten Tomatoes viewer rating greater than 4 and sort them by year in ascending order. | `db.movies.find({"tomatoes.viewer.rating": {$gt: 4}}).sort({year: 1})` | movies |
| Get the movie titles and plots for movies with at least one cast member in common with the movie "The Shawshank Redemption". | `db.movies.find({cast: {$in: (db.movies.findOne({title: "The Shawshank Redemption"}).cast)}}, {title: 1, plot: 1, _id: 0})` | movies |
| Find all movies with a runtime greater than 180 minutes and sort them by IMDB rating in descending order. | `db.movies.find({runtime: {$gt: 180}}).sort({"imdb.rating": -1})` | movies |
| Get the movie titles and genres for movies with more than 1000 IMDB votes and a Rotten Tomatoes critics rating greater than 90%. | `db.movies.find({"imdb.votes": {$gt: 1000}, "tomatoes.critic.rating": {$gt: 9}}, {title: 1, genres: 1, _id: 0})` | movies |
| Count the number of movies with a Rotten Tomatoes viewer rating greater than 4 and an IMDB rating greater than 8.5. | `db.movies.count({"tomatoes.viewer.rating": {$gt: 4}, "imdb.rating": {$gt: 8.5}})` | movies |
| Find all movies with the genre "Drama" and a runtime between 90 and 120 minutes, sorted by year in descending order. | `db.movies.find({genres: "Drama", runtime: {$gte: 90, $lte: 120}}).sort({year: -1})` | movies |
| Get the movie titles and directors for movies with more than 10 awards nominations. | `db.movies.find({"awards.nominations": {$gt: 10}}, {title: 1, directors: 1, _id: 0})` | movies |
| Find all movies with at least one writer in common with the movie "The Godfather". | `db.movies.find({writers: {$in: (db.movies.findOne({title: "The Godfather"}).writers)}})` | movies |
| Get the movie titles and plots for movies with a Rotten Tomatoes critics rating greater than 90% and an IMDB rating greater than 8.5, sorted by title. | `db.movies.find({"tomatoes.critic.rating": {$gt: 9}, "imdb.rating": {$gt: 8.5}}).sort({title: 1})` | movies |
| Count the number of movies with more than 10 awards wins and more than 1000 IMDB votes. | `db.movies.count({"awards.wins": {$gt: 10}, "imdb.votes": {$gt: 1000}})` | movies |
| Find all movies with the genre "Action" and a runtime between 90 and 120 minutes, sorted by IMDB rating in descending order. | `db.movies.find({genres: "Action", runtime: {$gte: 90, $lte: 120}}).sort({"imdb.rating": -1})` | movies |
| Get the movie titles and genres for movies with a Rotten Tomatoes viewer rating greater than 4 and an IMDB rating greater than 8.5, sorted by year in ascending order. | `db.movies.find({"tomatoes.viewer.rating": {$gt: 4}, "imdb.rating": {$gt: 8.5}}).sort({year: 1})` | movies |
| Find all movies with a runtime greater than 180 minutes and a Rotten Tomatoes viewer rating greater than 4. | `db.movies.find({runtime: {$gt: 180}, "tomatoes.viewer.rating": {$gt: 4}})` | movies |
| Count the number of movies directed by multiple directors. | `db.movies.count({directors: {$size: {$gt: 1}}})` | movies |
| Find all movies with a Rotten Tomatoes critics rating greater than 90% and an IMDB rating greater than 8.5, sorted by IMDB votes in descending order. | `db.movies.find({"tomatoes.critic.rating": {$gt: 9}, "imdb.rating": {$gt: 8.5}}).sort({"imdb.votes": -1})` | movies |
| Get the titles of movies with an IMDB rating between 7.5 and 8.0. | `db.movies.find({"imdb.rating": {$gte: 7.5, $lt: 8.0}}, {title: 1, _id: 0})` | movies |
| Find all movies with the genre "Romance" and sort them by IMDB rating in descending order. | `db.movies.find({genres: "Romance"}).sort({"imdb.rating": -1})` | movies |
| Get the movie titles and plots for movies with more than 10,000 IMDB votes. | `db.movies.find({"imdb.votes": {$gt: 10000}}, {title: 1, plot: 1, _id: 0})` | movies |
| Find all movies with at least one cast member in common with the movie "The Dark Knight". | `db.movies.find({cast: {$in: (db.movies.findOne({title: "The Dark Knight"}).cast)}})` | movies |
| Count the number of movies in each IMDB rating range (e.g., 0-2, 2-4, 4-6, 6-8, 8-10). | `db.movies.aggregate([{$bucket: {groupBy: "$imdb.rating", boundaries: [0, 2, 4, 6, 8, 10], default: "Other", output: {count: {$sum: 1}}}}, {$sort: {_id: 1}}])` | movies |
| Get the titles of movies with more than 5 countries in the "countries" array. | `db.movies.find({countries: {$size: {$gt: 5}}}, {title: 1, _id: 0})` | movies |
| Find all movies with a Rotten Tomatoes viewer rating greater than 4 and sort them by IMDB votes in descending order. | `db.movies.find({"tomatoes.viewer.rating": {$gt: 4}}).sort({"imdb.votes": -1})` | movies |
| Get the movie titles and directors for all movies with a runtime between 60 and 90 minutes. | `db.movies.find({runtime: {$gte: 60, $lte: 90}}, {title: 1, directors: 1, _id: 0})` | movies |
| Count the number of movies in each language. | `db.movies.aggregate([{$unwind: "$languages"}, {$group: {_id: "$languages", count: {$sum: 1}}}])` | movies |
| Find all movies with the word "Love" in the title. | `db.movies.find({title: /Love/i})` | movies |
| Get the movie titles and IMDB ratings for movies with more than 5 awards wins and more than 10,000 IMDB votes. | `db.movies.find({"awards.wins": {$gt: 5}, "imdb.votes": {$gt: 10000}}, {title: 1, "imdb.rating": 1, _id: 0})` | movies |
| Find all movies with at least one director in common with the movie "The Lord of the Rings: The Fellowship of the Ring". | `db.movies.find({directors: {$in: (db.movies.findOne({title: "The Lord of the Rings: The Fellowship of the Ring"}).directors)}})` | movies |
| Get the movie titles and genres for movies with a Rotten Tomatoes critics rating greater than 90% and an IMDB rating between 7.5 and 8.0. | `db.movies.find({"tomatoes.critic.rating": {$gt: 9}, "imdb.rating": {$gte: 7.5, $lt: 8.0}}, {title: 1, genres: 1, _id: 0})` | movies |
| Count the number of movies with more than 10 languages. | `db.movies.count({languages: {$size: {$gt: 10}}})` | movies |
| Find all movies with a Rotten Tomatoes viewer rating greater than 4 and sort them by IMDB rating in ascending order. | `db.movies.find({"tomatoes.viewer.rating": {$gt: 4}}).sort({"imdb.rating": 1})` | movies |
| Get the movie titles and plots for movies with at least one cast member in common with the movie "Forrest Gump". | `db.movies.find({cast: {$in: (db.movies.findOne({title: "Forrest Gump"}).cast)}}, {title: 1, plot: 1, _id: 0})` | movies |
| Find all movies with a runtime greater than 180 minutes and sort them by Rotten Tomatoes viewer rating in descending order. | `db.movies.find({runtime: {$gt: 180}}).sort({"tomatoes.viewer.rating": -1})` | movies |
| Get the movie titles and genres for movies with more than 1000 IMDB votes and a Rotten Tomatoes viewer rating greater than 4. | `db.movies.find({"imdb.votes": {$gt: 1000}, "tomatoes.viewer.rating": {$gt: 4}}, {title: 1, genres: 1, _id: 0})` | movies |
| Count the number of movies with a Rotten Tomatoes viewer rating greater than 4 and an IMDB rating between 7.5 and 8.0. | `db.movies.count({"tomatoes.viewer.rating": {$gt: 4}, "imdb.rating": {$gte: 7.5, $lt: 8.0}})` | movies |
| Get the movie titles and directors for movies with more than 10 awards nominations and a Rotten Tomatoes viewer rating greater than 4. | `db.movies.find({"awards.nominations": {$gt: 10}, "tomatoes.viewer.rating": {$gt: 4}}, {title: 1, directors: 1, _id: 0})` | movies |
| Find all movies with at least one writer in common with the movie "The Shawshank Redemption". | `db.movies.find({writers: {$in: (db.movies.findOne({title: "The Shawshank Redemption"}).writers)}})` | movies |
| Get the movie titles and plots for movies with a Rotten Tomatoes critics rating greater than 90% and an IMDB rating between 7.5 and 8.0, sorted by title. | `db.movies.find({"tomatoes.critic.rating": {$gt: 9}, "imdb.rating": {$gte: 7.5, $lt: 8.0}}).sort({title: 1})` | movies |
| Count the number of movies with more than 10 awards wins and more than 10,000 IMDB votes. | `db.movies.count({"awards.wins": {$gt: 10}, "imdb.votes": {$gt: 10000}})` | movies |
| Find all movies with the genre "Thriller" and a runtime between 90 and 120 minutes, sorted by IMDB rating in descending order. | `db.movies.find({genres: "Thriller", runtime: {$gte: 90, $lte: 120}}).sort({"imdb.rating": -1})` | movies |
| Get the movie titles and genres for movies with a Rotten Tomatoes viewer rating greater than 4 and an IMDB rating greater than 8.5, sorted by year in descending order. | `db.movies.find({"tomatoes.viewer.rating": {$gt: 4}, "imdb.rating": {$gt: 8.5}}).sort({year: -1})` | movies |
| Get the address details of theaters in the city of Los Angeles | `db.theaters.find({ "location.address.city": "Los Angeles" }, { "location.address": 1 })` | theaters |
| Find theaters with the street name "Broadway" | `db.theaters.find({ "location.address.street1": /Broadway/i })` | theaters |
| Get theaters with a location type other than "Point" | `db.theaters.find({ "location.geo.type": { $ne: "Point" } })` | theaters |
| Find theaters with coordinates within the range of longitude -122.5 to -122.3 and latitude 37.7 to 37.8 (San Francisco area) | `db.theaters.find({ "location.geo.coordinates": { $elemMatch: { $gte: [-122.5, 37.7], $lte: [-122.3, 37.8] } } })` | theaters |
| Get the count of theaters in each state of the United States | `db.theaters.aggregate([{ $group: { _id: "$location.address.state", count: { $sum: 1 } } }])` | theaters |
| Find theaters with the zip code "90001" (Los Angeles area) | `db.theaters.find({ "location.address.zipcode": "90001" })` | theaters |
| Get the theater with the maximum theater ID | `db.theaters.find().sort({ theaterId: -1 }).limit(1)` | theaters |
| Find theaters with multiple address lines | `db.theaters.find({ "location.address.street1": { $exists: true, $ne: "" } })` | theaters |
| Get theaters with latitude and longitude within the range of 40.6 to 40.9 and -74.1 to -73.9 (New York City area) | `db.theaters.find({ "location.geo.coordinates": { $elemMatch: { $gte: [-74.1, 40.6], $lte: [-73.9, 40.9] } } })` | theaters |
| Find theaters in the city of Chicago and state of Illinois | `db.theaters.find({ "location.address.city": "Chicago", "location.address.state": "IL" })` | theaters |
| Get the count of theaters in each city of California | `db.theaters.aggregate([{ $match: { "location.address.state": "CA" } }, { $group: { _id: "$location.address.city", count: { $sum: 1 } } }])` | theaters |
| Find the theater with the ID 1234 | `db.theaters.find({ theaterId: 1234 })` | theaters |
| Get the theater with the minimum theater ID | `db.theaters.find().sort({ theaterId: 1 }).limit(1)` | theaters |
| Get the count of theaters in each zip code of the state of Texas | `db.theaters.aggregate([{ $match: { "location.address.state": "TX" } }, { $group: { _id: "$location.address.zipcode", count: { $sum: 1 } } }])` | theaters |
| Find theaters on "Main Street" in the city of Seattle and state of Washington | `db.theaters.find({ "location.address.street1": "Main Street", "location.address.city": "Seattle", "location.address.state": "WA" })` | theaters |
| Get theaters with the longitude -87.6298 and latitude 41.8781 (Chicago area) | `db.theaters.find({ "location.geo.coordinates": [-87.6298, 41.8781] })` | theaters |
| Find theaters with coordinates within the bounding box of longitude -118.5 to -118.2 and latitude 34.0 to 34.2 (Los Angeles area) | `db.theaters.find({ location.geo: { $geoWithin: { $box: [ [-118.5, 34.0], [-118.2, 34.2] ] } } })` | theaters |
| Get the count of theaters with the location type "Point" | `db.theaters.aggregate([{ $match: { "location.geo.type": "Point" } }, { $group: { _id: null, count: { $sum: 1 } } }])` | theaters |
| Find theaters with the street number and name "123 Main St" | `db.theaters.find({ "location.address.street1": /^123 Main St/i })` | theaters |
| Get theaters in the zip code "60601" and city of Chicago | `db.theaters.find({ "location.address.zipcode": "60601", "location.address.city": "Chicago" })` | theaters |
| Find theaters in the state of Florida and zip code "33101" | `db.theaters.find({ "location.address.state": "FL", "location.address.zipcode": "33101" })` | theaters |
| Get the count of theaters on "Broadway" in New York City | `db.theaters.aggregate([{ $match: { "location.address.street1": /Broadway/i, "location.address.city": "New York" } }, { $group: { _id: null, count: { $sum: 1 } } }])` | theaters |
| Find theaters on "123 Main St" in the city of Los Angeles, state of California, and zip code "90001" | `db.theaters.find({ "location.address.street1": "123 Main St", "location.address.city": "Los Angeles", "location.address.state": "CA", "location.address.zipcode": "90001" })` | theaters |
| Get theaters with the street suffix "Ave" in the city of Boston | `db.theaters.find({ "location.address.street1": /Ave\\b/i, "location.address.city": "Boston" })` | theaters |
| Find theaters with the location type "Point" and coordinates within the range of longitude -122.5 to -122.3 and latitude 37.7 to 37.8 (San Francisco area) | `db.theaters.find({ "location.geo.type": "Point", "location.geo.coordinates": { $elemMatch: { $gte: [-122.5, 37.7], $lte: [-122.3, 37.8] } } })` | theaters |
| Get the count of theaters in the city of Miami and state of Florida | `db.theaters.aggregate([{ $match: { "location.address.city": "Miami", "location.address.state": "FL" } }, { $group: { _id: null, count: { $sum: 1 } } }])` | theaters |
| Find theaters with the street direction "N" or "S" in the city of Philadelphia | `db.theaters.find({ "location.address.street1": /\\b(N|S)\\b/i, "location.address.city": "Philadelphia" })` | theaters |
| Find theaters on streets starting with "North" in the city of Seattle | `db.theaters.find({ "location.address.street1": /^North/i, "location.address.city": "Seattle" })` | theaters |
| Get the count of theaters in zip codes starting with "900" (Los Angeles area) | `db.theaters.aggregate([{ $match: { "location.address.zipcode": /^900/ } }, { $group: { _id: null, count: { $sum: 1 } } }])` | theaters |
| Find theaters with coordinates within a 10-mile radius of the Eiffel Tower, Paris | `db.theaters.find({ location.geo: { $near: { $geometry: { type: "Point", coordinates: [2.2945, 48.8584] }, $maxDistance: 10 * 1609.34 } } })` | theaters |
| Get theaters with the street number "1234" in the state of Texas | `db.theaters.find({ "location.address.street1": /^1234/i, "location.address.state": "TX" })` | theaters |
| Find theaters with the word "Theater" in their street address in the city of Chicago | `db.theaters.find({ "location.address.street1": /Theater/i, "location.address.city": "Chicago" })` | theaters |
| Get the count of theaters in each city of the state of Florida | `db.theaters.aggregate([{ $match: { "location.address.state": "FL" } }, { $group: { _id: "$location.address.city", count: { $sum: 1 } } }])` | theaters |
| Find theaters with the street suffix "Blvd" in the city of Denver | `db.theaters.find({ "location.address.street1": /Blvd\\b/i, "location.address.city": "Denver" })` | theaters |
| Find theaters on streets ending with "Road" in the city of Atlanta | `db.theaters.find({ "location.address.street1": /Road$/i, "location.address.city": "Atlanta" })` | theaters |
| Get the count of theaters with the location type "MultiPoint" | `db.theaters.aggregate([{ $match: { "location.geo.type": "MultiPoint" } }, { $group: { _id: null, count: { $sum: 1 } } }])` | theaters |
| Find theaters with the zip code "60605" in the city of Chicago | `db.theaters.find({ "location.address.zipcode": "60605", "location.address.city": "Chicago" })` | theaters |
| Get theaters with coordinates within a 2-mile radius of the London Eye, London | `db.theaters.find({ location.geo: { $near: { $geometry: { type: "Point", coordinates: [-0.1198, 51.5033] }, $maxDistance: 2 * 1609.34 } } })` | theaters |
| Find theaters with the street direction "E" or "W" in the city of Houston | `db.theaters.find({ "location.address.street1": /\\b(E|W)\\b/i, "location.address.city": "Houston" })` | theaters |
| Get the count of theaters in each state starting with "M" | `db.theaters.aggregate([{ $match: { "location.address.state": /^M/ } }, { $group: { _id: "$location.address.state", count: { $sum: 1 } } }])` | theaters |
| Find theaters with the street number "456" and street name "Oak Street" | `db.theaters.find({ "location.address.street1": /^456 Oak St/i })` | theaters |
| Get theaters with coordinates within the bounding box of longitude -77.15 to -77.0 and latitude 38.85 to 38.95 (Washington D.C. area) | `db.theaters.find({ location.geo: { $geoWithin: { $box: [ [-77.15, 38.85], [-77.0, 38.95] ] } } })` | theaters |
| Find theaters on streets containing the word "Park" in the city of San Diego | `db.theaters.find({ "location.address.street1": /Park/i, "location.address.city": "San Diego" })` | theaters |
| Get the count of theaters in zip codes ending with "01" in the state of Massachusetts | `db.theaters.aggregate([{ $match: { "location.address.state": "MA", "location.address.zipcode": /01$/ } }, { $group: { _id: null, count: { $sum: 1 } } }])` | theaters |
| Find theaters with the street direction "NE" or "NW" in the city of Portland | `db.theaters.find({ "location.address.street1": /\\b(NE|NW)\\b/i, "location.address.city": "Portland" })` | theaters |
| Get theaters with coordinates within a 3-mile radius of the Sydney Opera House, Sydney | `db.theaters.find({ location.geo: { $near: { $geometry: { type: "Point", coordinates: [151.2153, -33.8568] }, $maxDistance: 3 * 1609.34 } } })` | theaters |
| Find theaters with the street number "789" and street name "Maple Avenue" in the city of Boston | `db.theaters.find({ "location.address.street1": /^789 Maple Ave/i, "location.address.city": "Boston" })` | theaters |
| Get the count of theaters in zip codes starting with "900" in the state of California | `db.theaters.aggregate([{ $match: { "location.address.state": "CA", "location.address.zipcode": /^900/ } }, { $group: { _id: null, count: { $sum: 1 } } }])` | theaters |
| Find theaters with the word "Cinema" in their street address in the city of Miami | `db.theaters.find({ "location.address.street1": /Cinema/i, "location.address.city": "Miami" })` | theaters |
| Find theaters on streets ending with "Lane" in the city of Austin | `db.theaters.find({ "location.address.street1": /Lane$/i, "location.address.city": "Austin" })` | theaters |
| Get the count of theaters with the location type "LineString" | `db.theaters.aggregate([{ $match: { "location.geo.type": "LineString" } }, { $group: { _id: null, count: { $sum: 1 } } }])` | theaters |
| Find theaters with the zip code "90068" in the city of Los Angeles | `db.theaters.find({ "location.address.zipcode": "90068", "location.address.city": "Los Angeles" })` | theaters |
| Get theaters with coordinates within a 5-mile radius of the Golden Gate Bridge, San Francisco | `db.theaters.find({ location.geo: { $near: { $geometry: { type: "Point", coordinates: [-122.4783, 37.8199] }, $maxDistance: 5 * 1609.34 } } })` | theaters |
| Find theaters with the street direction "SE" or "SW" in the city of Phoenix | `db.theaters.find({ "location.address.street1": /\\b(SE|SW)\\b/i, "location.address.city": "Phoenix" })` | theaters |
| Find customers with multiple accounts | `db.customers.find({"accounts": {"$exists": true, "$not": {"$size": 0}}})` | customers |
| Find customers with tier level 'premium' | `db.customers.find({"tier_and_details.tier": "premium"})` | customers |
| Find customers with benefit 'free_shipping' | `db.customers.find({"tier_and_details.benefits": "free_shipping"})` | customers |
| Find active customers | `db.customers.find({"tier_and_details.active": true})` | customers |
| Find customers with tier ID 'PREM001' | `db.customers.find({"tier_and_details.id": "PREM001"})` | customers |
| Find customers born in 1980 | `db.customers.find({"birthdate": {"$gte": new Date("1980-01-01"), "$lt": new Date("1981-01-01")}})` | customers |
| Find customers with name 'John Doe' and address '123 Main St' | `db.customers.find({"name": "John Doe", "address": "123 Main St"})` | customers |
| Count customers with tier level 'premium' | `db.customers.countDocuments({"tier_and_details.tier": "premium"})` | customers |
| Group customers by tier level | `db.customers.aggregate([{"$group": {"_id": "$tier_and_details.tier", "count": {"$sum": 1}}}])` | customers |
| Find customers with name 'John Doe' and tier level 'premium' | `db.customers.find({"name": "John Doe", "tier_and_details.tier": "premium"})` | customers |
| Find customers with account 123456 and tier level 'premium' | `db.customers.find({"accounts": 123456, "tier_and_details.tier": "premium"})` | customers |
| Find customers with benefit 'free_shipping' and active status true | `db.customers.find({"tier_and_details.benefits": "free_shipping", "tier_and_details.active": true})` | customers |
| Find customers with multiple benefits | `db.customers.find({"tier_and_details.benefits": {"$exists": true, "$not": {"$size": 0}}})` | customers |
| Find customers with name 'John Doe' born in 1980 | `db.customers.find({"name": "John Doe", "birthdate": {"$gte": new Date("1980-01-01"), "$lt": new Date("1981-01-01")}})` | customers |
| Find customers with tier level 'premium' born in 1980 | `db.customers.find({"tier_and_details.tier": "premium", "birthdate": {"$gte": new Date("1980-01-01"), "$lt": new Date("1981-01-01")}})` | customers |
| Find customers with email 'john@example.com' and account 123456 | `db.customers.find({"email": "john@example.com", "accounts": 123456})` | customers |
| Find customers with username 'john_doe' and tier ID 'PREM001' | `db.customers.find({"username": "john_doe", "tier_and_details.id": "PREM001"})` | customers |
| Find customers with name 'John Doe' and multiple accounts | `db.customers.find({"name": "John Doe", "accounts": {"$exists": true, "$not": {"$size": 0}}})` | customers |
| Find customers with address '123 Main St' and benefit 'free_shipping' | `db.customers.find({"address": "123 Main St", "tier_and_details.benefits": "free_shipping"})` | customers |
| Find customers born in 1980 with tier ID 'PREM001' | `db.customers.find({"birthdate": {"$gte": new Date("1980-01-01"), "$lt": new Date("1981-01-01")}, "tier_and_details.id": "PREM001"})` | customers |
| Find customers with email 'john@example.com' and multiple benefits | `db.customers.find({"email": "john@example.com", "tier_and_details.benefits": {"$exists": true, "$not": {"$size": 0}}})` | customers |
| Find customers with username 'john_doe' and inactive status | `db.customers.find({"username": "john_doe", "tier_and_details.active": false})` | customers |
| Find customers with name 'John Doe', tier ID 'PREM001', and benefit 'free_shipping' | `db.customers.find({"name": "John Doe", "tier_and_details.id": "PREM001", "tier_and_details.benefits": "free_shipping"})` | customers |
| Find customers with tier level not 'premium' | `db.customers.find({"tier_and_details.tier": {"$ne": "premium"}})` | customers |
| Find customers without any benefits | `db.customers.find({"tier_and_details.benefits": []})` | customers |
| Find customers with inactive status | `db.customers.find({"tier_and_details.active": false})` | customers |
| Find customers with tier ID 'PREM001' or 'PREM002' | `db.customers.find({"tier_and_details.id": {"$in": ["PREM001", "PREM002"]}})` | customers |
| Find customers with name 'John Doe' or 'Jane Smith' | `db.customers.find({"name": {"$in": ["John Doe", "Jane Smith"]}})` | customers |
| Count customers with tier level 'basic' | `db.customers.countDocuments({"tier_and_details.tier": "basic"})` | customers |
| Group customers by active status | `db.customers.aggregate([{"$group": {"_id": "$tier_and_details.active", "count": {"$sum": 1}}}])` | customers |
| Find customers with name not 'John Doe' and tier level not 'premium' | `db.customers.find({"name": {"$ne": "John Doe"}, "tier_and_details.tier": {"$ne": "premium"}})` | customers |
| Find customers with account not 123456 and tier level 'premium' | `db.customers.find({"accounts": {"$ne": 123456}, "tier_and_details.tier": "premium"})` | customers |
| Find customers with benefit 'free_shipping' and inactive status | `db.customers.find({"tier_and_details.benefits": "free_shipping", "tier_and_details.active": false})` | customers |
| Find customers with multiple benefits and active status | `db.customers.find({"tier_and_details.benefits": {"$exists": true, "$not": {"$size": 0}}, "tier_and_details.active": true})` | customers |
| Find customers with tier level 'premium' not born in 1980 | `db.customers.find({"tier_and_details.tier": "premium", "birthdate": {"$not": {"$gte": new Date("1980-01-01"), "$lt": new Date("1981-01-01")}}})` | customers |
| Find customers with username not 'john_doe' and tier ID 'PREM001' | `db.customers.find({"username": {"$ne": "john_doe"}, "tier_and_details.id": "PREM001"})` | customers |
| Find customers with name not 'John Doe' and multiple accounts | `db.customers.find({"name": {"$ne": "John Doe"}, "accounts": {"$exists": true, "$not": {"$size": 0}}})` | customers |
| Find customers with address not '123 Main St' and benefit 'free_shipping' | `db.customers.find({"address": {"$ne": "123 Main St"}, "tier_and_details.benefits": "free_shipping"})` | customers |
| Find customers not born in 1980 with tier ID 'PREM001' | `db.customers.find({"birthdate": {"$not": {"$gte": new Date("1980-01-01"), "$lt": new Date("1981-01-01")}}, "tier_and_details.id": "PREM001"})` | customers |
| Find customers with email not 'john@example.com' and multiple benefits | `db.customers.find({"email": {"$ne": "john@example.com"}, "tier_and_details.benefits": {"$exists": true, "$not": {"$size": 0}}})` | customers |
| Find customers with username not 'john_doe' and active status | `db.customers.find({"username": {"$ne": "john_doe"}, "tier_and_details.active": true})` | customers |
| Find customers with name not 'John Doe', tier ID not 'PREM001', and benefit not 'free_shipping' | `db.customers.find({"name": {"$ne": "John Doe"}, "tier_and_details.id": {"$ne": "PREM001"}, "tier_and_details.benefits": {"$ne": "free_shipping"}})` | customers |
| How many transactions were made for a particular account (e.g., account_id = 12345) during a specific date range (e.g., between 2023-01-01 and 2023-01-31)? | `db.transactions.aggregate([{$match: {account_id: 12345, bucket_start_date: {$gte: new Date('2023-01-01'), $lte: new Date('2023-01-31')}}}, {$project: {transaction_count: 1}}])` | transactions |
| What is the total amount of transactions for a particular account (e.g., account_id = 12345) during a specific date range (e.g., between 2023-01-01 and 2023-01-31)? | `db.transactions.aggregate([{$match: {account_id: 12345, bucket_start_date: {$gte: new Date('2023-01-01'), $lte: new Date('2023-01-31')}}}, {$unwind: '$transactions'}, {$group: {_id: null, total_amount: {$sum: {$toInt: '$transactions.total'}}}}])` | transactions |
| What are the distinct transaction codes for a particular account (e.g., account_id = 12345) during a specific date range (e.g., between 2023-01-01 and 2023-01-31)? | `db.transactions.aggregate([{$match: {account_id: 12345, bucket_start_date: {$gte: new Date('2023-01-01'), $lte: new Date('2023-01-31')}}}, {$unwind: '$transactions'}, {$group: {_id: null, distinct_transaction_codes: {$addToSet: '$transactions.transaction_code'}}}])` | transactions |
| What are the top 5 symbols with the highest total transaction amount for a particular account (e.g., account_id = 12345) during a specific date range (e.g., between 2023-01-01 and 2023-01-31)? | `db.transactions.aggregate([{$match: {account_id: 12345, bucket_start_date: {$gte: new Date('2023-01-01'), $lte: new Date('2023-01-31')}}}, {$unwind: '$transactions'}, {$group: {_id: '$transactions.symbol', total_amount: {$sum: {$toInt: '$transactions.total'}}}}, {$sort: {total_amount: -1}}, {$limit: 5}])` | transactions |
| What is the average transaction amount for a particular account (e.g., account_id = 12345) during a specific date range (e.g., between 2023-01-01 and 2023-01-31)? | `db.transactions.aggregate([{$match: {account_id: 12345, bucket_start_date: {$gte: new Date('2023-01-01'), $lte: new Date('2023-01-31')}}}, {$unwind: '$transactions'}, {$group: {_id: null, avg_amount: {$avg: {$toInt: '$transactions.total'}}}}])` | transactions |
| What are the distinct symbols traded for a particular account (e.g., account_id = 12345) during a specific date range (e.g., between 2023-01-01 and 2023-01-31)? | `db.transactions.aggregate([{$match: {account_id: 12345, bucket_start_date: {$gte: new Date('2023-01-01'), $lte: new Date('2023-01-31')}}}, {$unwind: '$transactions'}, {$group: {_id: null, distinct_symbols: {$addToSet: '$transactions.symbol'}}}])` | transactions |
| What is the maximum transaction amount for a particular account (e.g., account_id = 12345) during a specific date range (e.g., between 2023-01-01 and 2023-01-31)? | `db.transactions.aggregate([{$match: {account_id: 12345, bucket_start_date: {$gte: new Date('2023-01-01'), $lte: new Date('2023-01-31')}}}, {$unwind: '$transactions'}, {$group: {_id: null, max_amount: {$max: {$toInt: '$transactions.total'}}}}])` | transactions |
| What is the minimum transaction amount for a particular account (e.g., account_id = 12345) during a specific date range (e.g., between 2023-01-01 and 2023-01-31)? | `db.transactions.aggregate([{$match: {account_id: 12345, bucket_start_date: {$gte: new Date('2023-01-01'), $lte: new Date('2023-01-31')}}}, {$unwind: '$transactions'}, {$group: {_id: null, min_amount: {$min: {$toInt: '$transactions.total'}}}}])` | transactions |
| What are the top 3 transaction codes with the highest total transaction amount for a particular account (e.g., account_id = 12345) during a specific date range (e.g., between 2023-01-01 and 2023-01-31)? | `db.transactions.aggregate([{$match: {account_id: 12345, bucket_start_date: {$gte: new Date('2023-01-01'), $lte: new Date('2023-01-31')}}}, {$unwind: '$transactions'}, {$group: {_id: '$transactions.transaction_code', total_amount: {$sum: {$toInt: '$transactions.total'}}}}, {$sort: {total_amount: -1}}, {$limit: 3}])` | transactions |
| What is the count of transactions for each transaction code for a particular account (e.g., account_id = 12345) during a specific date range (e.g., between 2023-01-01 and 2023-01-31)? | `db.transactions.aggregate([{$match: {account_id: 12345, bucket_start_date: {$gte: new Date('2023-01-01'), $lte: new Date('2023-01-31')}}}, {$unwind: '$transactions'}, {$group: {_id: '$transactions.transaction_code', transaction_count: {$sum: 1}}}])` | transactions |
| What are the distinct dates when transactions were made for a particular account (e.g., account_id = 12345) during a specific date range (e.g., between 2023-01-01 and 2023-01-31)? | `db.transactions.aggregate([{$match: {account_id: 12345, bucket_start_date: {$gte: new Date('2023-01-01'), $lte: new Date('2023-01-31')}}}, {$unwind: '$transactions'}, {$group: {_id: null, distinct_dates: {$addToSet: '$transactions.date'}}}])` | transactions |
| What is the total transaction amount for each symbol for a particular account (e.g., account_id = 12345) during a specific date range (e.g., between 2023-01-01 and 2023-01-31)? | `db.transactions.aggregate([{$match: {account_id: 12345, bucket_start_date: {$gte: new Date('2023-01-01'), $lte: new Date('2023-01-31')}}}, {$unwind: '$transactions'}, {$group: {_id: '$transactions.symbol', total_amount: {$sum: {$toInt: '$transactions.total'}}}}])` | transactions |
| What is the average transaction amount for each transaction code for a particular account (e.g., account_id = 12345) during a specific date range (e.g., between 2023-01-01 and 2023-01-31)? | `db.transactions.aggregate([{$match: {account_id: 12345, bucket_start_date: {$gte: new Date('2023-01-01'), $lte: new Date('2023-01-31')}}}, {$unwind: '$transactions'}, {$group: {_id: '$transactions.transaction_code', avg_amount: {$avg: {$toInt: '$transactions.total'}}}}])` | transactions |
| What is the count of transactions for each symbol for a particular account (e.g., account_id = 12345) during a specific date range (e.g., between 2023-01-01 and 2023-01-31)? | `db.transactions.aggregate([{$match: {account_id: 12345, bucket_start_date: {$gte: new Date('2023-01-01'), $lte: new Date('2023-01-31')}}}, {$unwind: '$transactions'}, {$group: {_id: '$transactions.symbol', transaction_count: {$sum: 1}}}])` | transactions |
| What are the top 3 symbols with the highest transaction count for a particular account (e.g., account_id = 12345) during a specific date range (e.g., between 2023-01-01 and 2023-01-31)? | `db.transactions.aggregate([{$match: {account_id: 12345, bucket_start_date: {$gte: new Date('2023-01-01'), $lte: new Date('2023-01-31')}}}, {$unwind: '$transactions'}, {$group: {_id: '$transactions.symbol', transaction_count: {$sum: 1}}}, {$sort: {transaction_count: -1}}, {$limit: 3}])` | transactions |
| What is the maximum transaction amount for each symbol for a particular account (e.g., account_id = 12345) during a specific date range (e.g., between 2023-01-01 and 2023-01-31)? | `db.transactions.aggregate([{$match: {account_id: 12345, bucket_start_date: {$gte: new Date('2023-01-01'), $lte: new Date('2023-01-31')}}}, {$unwind: '$transactions'}, {$group: {_id: '$transactions.symbol', max_amount: {$max: {$toInt: '$transactions.total'}}}}])` | transactions |
| What is the minimum transaction amount for each symbol for a particular account (e.g., account_id = 12345) during a specific date range (e.g., between 2023-01-01 and 2023-01-31)? | `db.transactions.aggregate([{$match: {account_id: 12345, bucket_start_date: {$gte: new Date('2023-01-01'), $lte: new Date('2023-01-31')}}}, {$unwind: '$transactions'}, {$group: {_id: '$transactions.symbol', min_amount: {$min: {$toInt: '$transactions.total'}}}}])` | transactions |
| What is the total transaction amount for each transaction code for a particular account (e.g., account_id = 12345) during a specific date range (e.g., between 2023-01-01 and 2023-01-31)? | `db.transactions.aggregate([{$match: {account_id: 12345, bucket_start_date: {$gte: new Date('2023-01-01'), $lte: new Date('2023-01-31')}}}, {$unwind: '$transactions'}, {$group: {_id: '$transactions.transaction_code', total_amount: {$sum: {$toInt: '$transactions.total'}}}}])` | transactions |
| What is the count of transactions for each date for a particular account (e.g., account_id = 12345) during a specific date range (e.g., between 2023-01-01 and 2023-01-31)? | `db.transactions.aggregate([{$match: {account_id: 12345, bucket_start_date: {$gte: new Date('2023-01-01'), $lte: new Date('2023-01-31')}}}, {$unwind: '$transactions'}, {$group: {_id: '$transactions.date', transaction_count: {$sum: 1}}}])` | transactions |
| What are the top 5 dates with the highest total transaction amount for a particular account (e.g., account_id = 12345) during a specific date range (e.g., between 2023-01-01 and 2023-01-31)? | `db.transactions.aggregate([{$match: {account_id: 12345, bucket_start_date: {$gte: new Date('2023-01-01'), $lte: new Date('2023-01-31')}}}, {$unwind: '$transactions'}, {$group: {_id: '$transactions.date', total_amount: {$sum: {$toInt: '$transactions.total'}}}}, {$sort: {total_amount: -1}}, {$limit: 5}])` | transactions |
| What is the average transaction price for each symbol for a particular account (e.g., account_id = 12345) during a specific date range (e.g., between 2023-01-01 and 2023-01-31)? | `db.transactions.aggregate([{$match: {account_id: 12345, bucket_start_date: {$gte: new Date('2023-01-01'), $lte: new Date('2023-01-31')}}}, {$unwind: '$transactions'}, {$group: {_id: '$transactions.symbol', avg_price: {$avg: {$toDouble: '$transactions.price'}}}}])` | transactions |
| What are the top 3 symbols with the highest average transaction price for a particular account (e.g., account_id = 12345) during a specific date range (e.g., between 2023-01-01 and 2023-01-31)? | `db.transactions.aggregate([{$match: {account_id: 12345, bucket_start_date: {$gte: new Date('2023-01-01'), $lte: new Date('2023-01-31')}}}, {$unwind: '$transactions'}, {$group: {_id: '$transactions.symbol', avg_price: {$avg: {$toDouble: '$transactions.price'}}}}, {$sort: {avg_price: -1}}, {$limit: 3}])` | transactions |
| What is the maximum transaction price for each symbol for a particular account (e.g., account_id = 12345) during a specific date range (e.g., between 2023-01-01 and 2023-01-31)? | `db.transactions.aggregate([{$match: {account_id: 12345, bucket_start_date: {$gte: new Date('2023-01-01'), $lte: new Date('2023-01-31')}}}, {$unwind: '$transactions'}, {$group: {_id: '$transactions.symbol', max_price: {$max: {$toDouble: '$transactions.price'}}}}])` | transactions |
| What is the minimum transaction price for each symbol for a particular account (e.g., account_id = 12345) during a specific date range (e.g., between 2023-01-01 and 2023-01-31)? | `db.transactions.aggregate([{$match: {account_id: 12345, bucket_start_date: {$gte: new Date('2023-01-01'), $lte: new Date('2023-01-31')}}}, {$unwind: '$transactions'}, {$group: {_id: '$transactions.symbol', min_price: {$min: {$toDouble: '$transactions.price'}}}}])` | transactions |
| How many transactions were made for account 54321 between 2023-02-01 and 2023-02-28? | `db.transactions.aggregate([{$match: {account_id: 54321, bucket_start_date: {$gte: new Date('2023-02-01'), $lte: new Date('2023-02-28')}}}, {$project: {transaction_count: 1}}])` | transactions |
| What is the total amount of transactions for account 67890 between 2023-03-01 and 2023-03-31? | `db.transactions.aggregate([{$match: {account_id: 67890, bucket_start_date: {$gte: new Date('2023-03-01'), $lte: new Date('2023-03-31')}}}, {$unwind: '$transactions'}, {$group: {_id: null, total_amount: {$sum: {$toInt: '$transactions.total'}}}}])` | transactions |
| What are the distinct transaction codes for account 24680 between 2023-04-01 and 2023-04-30? | `db.transactions.aggregate([{$match: {account_id: 24680, bucket_start_date: {$gte: new Date('2023-04-01'), $lte: new Date('2023-04-30')}}}, {$unwind: '$transactions'}, {$group: {_id: null, distinct_transaction_codes: {$addToSet: '$transactions.transaction_code'}}}])` | transactions |
| What are the top 5 symbols with the highest total transaction amount for account 13579 between 2023-05-01 and 2023-05-31? | `db.transactions.aggregate([{$match: {account_id: 13579, bucket_start_date: {$gte: new Date('2023-05-01'), $lte: new Date('2023-05-31')}}}, {$unwind: '$transactions'}, {$group: {_id: '$transactions.symbol', total_amount: {$sum: {$toInt: '$transactions.total'}}}}, {$sort: {total_amount: -1}}, {$limit: 5}])` | transactions |
| What is the average transaction amount for account 97531 between 2023-06-01 and 2023-06-30? | `db.transactions.aggregate([{$match: {account_id: 97531, bucket_start_date: {$gte: new Date('2023-06-01'), $lte: new Date('2023-06-30')}}}, {$unwind: '$transactions'}, {$group: {_id: null, avg_amount: {$avg: {$toInt: '$transactions.total'}}}}])` | transactions |
| What are the distinct symbols traded for account 86420 between 2023-07-01 and 2023-07-31? | `db.transactions.aggregate([{$match: {account_id: 86420, bucket_start_date: {$gte: new Date('2023-07-01'), $lte: new Date('2023-07-31')}}}, {$unwind: '$transactions'}, {$group: {_id: null, distinct_symbols: {$addToSet: '$transactions.symbol'}}}])` | transactions |
| What is the maximum transaction amount for account 75319 between 2023-08-01 and 2023-08-31? | `db.transactions.aggregate([{$match: {account_id: 75319, bucket_start_date: {$gte: new Date('2023-08-01'), $lte: new Date('2023-08-31')}}}, {$unwind: '$transactions'}, {$group: {_id: null, max_amount: {$max: {$toInt: '$transactions.total'}}}}])` | transactions |
| What is the minimum transaction amount for account 63258 between 2023-09-01 and 2023-09-30? | `db.transactions.aggregate([{$match: {account_id: 63258, bucket_start_date: {$gte: new Date('2023-09-01'), $lte: new Date('2023-09-30')}}}, {$unwind: '$transactions'}, {$group: {_id: null, min_amount: {$min: {$toInt: '$transactions.total'}}}}])` | transactions |
| What are the top 3 transaction codes with the highest total transaction amount for account 51497 between 2023-10-01 and 2023-10-31? | `db.transactions.aggregate([{$match: {account_id: 51497, bucket_start_date: {$gte: new Date('2023-10-01'), $lte: new Date('2023-10-31')}}}, {$unwind: '$transactions'}, {$group: {_id: '$transactions.transaction_code', total_amount: {$sum: {$toInt: '$transactions.total'}}}}, {$sort: {total_amount: -1}}, {$limit: 3}])` | transactions |
| What is the count of transactions for each transaction code for account 39736 between 2023-11-01 and 2023-11-30? | `db.transactions.aggregate([{$match: {account_id: 39736, bucket_start_date: {$gte: new Date('2023-11-01'), $lte: new Date('2023-11-30')}}}, {$unwind: '$transactions'}, {$group: {_id: '$transactions.transaction_code', transaction_count: {$sum: 1}}}])` | transactions |
| What are the distinct dates when transactions were made for account 27975 between 2023-12-01 and 2023-12-31? | `db.transactions.aggregate([{$match: {account_id: 27975, bucket_start_date: {$gte: new Date('2023-12-01'), $lte: new Date('2023-12-31')}}}, {$unwind: '$transactions'}, {$group: {_id: null, distinct_dates: {$addToSet: '$transactions.date'}}}])` | transactions |
| What is the total transaction amount for each symbol for account 16214 between 2024-01-01 and 2024-01-31? | `db.transactions.aggregate([{$match: {account_id: 16214, bucket_start_date: {$gte: new Date('2024-01-01'), $lte: new Date('2024-01-31')}}}, {$unwind: '$transactions'}, {$group: {_id: '$transactions.symbol', total_amount: {$sum: {$toInt: '$transactions.total'}}}}])` | transactions |
| What is the average transaction amount for each transaction code for account 4453 between 2024-02-01 and 2024-02-29? | `db.transactions.aggregate([{$match: {account_id: 4453, bucket_start_date: {$gte: new Date('2024-02-01'), $lte: new Date('2024-02-29')}}}, {$unwind: '$transactions'}, {$group: {_id: '$transactions.transaction_code', avg_amount: {$avg: {$toInt: '$transactions.total'}}}}])` | transactions |
| What is the count of transactions for each symbol for account 92682 between 2024-03-01 and 2024-03-31? | `db.transactions.aggregate([{$match: {account_id: 92682, bucket_start_date: {$gte: new Date('2024-03-01'), $lte: new Date('2024-03-31')}}}, {$unwind: '$transactions'}, {$group: {_id: '$transactions.symbol', transaction_count: {$sum: 1}}}])` | transactions |
| What are the top 3 symbols with the highest transaction count for account 80921 between 2024-04-01 and 2024-04-30? | `db.transactions.aggregate([{$match: {account_id: 80921, bucket_start_date: {$gte: new Date('2024-04-01'), $lte: new Date('2024-04-30')}}}, {$unwind: '$transactions'}, {$group: {_id: '$transactions.symbol', transaction_count: {$sum: 1}}}, {$sort: {transaction_count: -1}}, {$limit: 3}])` | transactions |
| What is the maximum transaction amount for each symbol for account 69160 between 2024-05-01 and 2024-05-31? | `db.transactions.aggregate([{$match: {account_id: 69160, bucket_start_date: {$gte: new Date('2024-05-01'), $lte: new Date('2024-05-31')}}}, {$unwind: '$transactions'}, {$group: {_id: '$transactions.symbol', max_amount: {$max: {$toInt: '$transactions.total'}}}}])` | transactions |
| What is the minimum transaction amount for each symbol for account 57399 between 2024-06-01 and 2024-06-30? | `db.transactions.aggregate([{$match: {account_id: 57399, bucket_start_date: {$gte: new Date('2024-06-01'), $lte: new Date('2024-06-30')}}}, {$unwind: '$transactions'}, {$group: {_id: '$transactions.symbol', min_amount: {$min: {$toInt: '$transactions.total'}}}}])` | transactions |
| What is the total transaction amount for each transaction code for account 45638 between 2024-07-01 and 2024-07-31? | `db.transactions.aggregate([{$match: {account_id: 45638, bucket_start_date: {$gte: new Date('2024-07-01'), $lte: new Date('2024-07-31')}}}, {$unwind: '$transactions'}, {$group: {_id: '$transactions.transaction_code', total_amount: {$sum: {$toInt: '$transactions.total'}}}}])` | transactions |
| What is the count of transactions for each date for account 33877 between 2024-08-01 and 2024-08-31? | `db.transactions.aggregate([{$match: {account_id: 33877, bucket_start_date: {$gte: new Date('2024-08-01'), $lte: new Date('2024-08-31')}}}, {$unwind: '$transactions'}, {$group: {_id: '$transactions.date', transaction_count: {$sum: 1}}}])` | transactions |
| What are the top 5 dates with the highest total transaction amount for account 22116 between 2024-09-01 and 2024-09-30? | `db.transactions.aggregate([{$match: {account_id: 22116, bucket_start_date: {$gte: new Date('2024-09-01'), $lte: new Date('2024-09-30')}}}, {$unwind: '$transactions'}, {$group: {_id: '$transactions.date', total_amount: {$sum: {$toInt: '$transactions.total'}}}}, {$sort: {total_amount: -1}}, {$limit: 5}])` | transactions |
| What is the average transaction price for each symbol for account 10355 between 2024-10-01 and 2024-10-31? | `db.transactions.aggregate([{$match: {account_id: 10355, bucket_start_date: {$gte: new Date('2024-10-01'), $lte: new Date('2024-10-31')}}}, {$unwind: '$transactions'}, {$group: {_id: '$transactions.symbol', avg_price: {$avg: {$toDouble: '$transactions.price'}}}}])` | transactions |
| What are the top 3 symbols with the highest average transaction price for account 98594 between 2024-11-01 and 2024-11-30? | `db.transactions.aggregate([{$match: {account_id: 98594, bucket_start_date: {$gte: new Date('2024-11-01'), $lte: new Date('2024-11-30')}}}, {$unwind: '$transactions'}, {$group: {_id: '$transactions.symbol', avg_price: {$avg: {$toDouble: '$transactions.price'}}}}, {$sort: {avg_price: -1}}, {$limit: 3}])` | transactions |
| What is the maximum transaction price for each symbol for account 86833 between 2024-12-01 and 2024-12-31? | `db.transactions.aggregate([{$match: {account_id: 86833, bucket_start_date: {$gte: new Date('2024-12-01'), $lte: new Date('2024-12-31')}}}, {$unwind: '$transactions'}, {$group: {_id: '$transactions.symbol', max_price: {$max: {$toDouble: '$transactions.price'}}}}])` | transactions |
| What is the minimum transaction price for each symbol for account 75072 between 2025-01-01 and 2025-01-31? | `db.transactions.aggregate([{$match: {account_id: 75072, bucket_start_date: {$gte: new Date('2025-01-01'), $lte: new Date('2025-01-31')}}}, {$unwind: '$transactions'}, {$group: {_id: '$transactions.symbol', min_price: {$min: {$toDouble: '$transactions.price'}}}}])` | transactions |
| How many transactions were made for account 63311 between 2025-02-01 and 2025-02-28? | `db.transactions.aggregate([{$match: {account_id: 63311, bucket_start_date: {$gte: new Date('2025-02-01'), $lte: new Date('2025-02-28')}}}, {$project: {transaction_count: 1}}])` | transactions |
| What is the total amount of transactions for account 51550 between 2025-03-01 and 2025-03-31? | `db.transactions.aggregate([{$match: {account_id: 51550, bucket_start_date: {$gte: new Date('2025-03-01'), $lte: new Date('2025-03-31')}}}, {$unwind: '$transactions'}, {$group: {_id: null, total_amount: {$sum: {$toInt: '$transactions.total'}}}}])` | transactions |
| What are the distinct transaction codes for account 39789 between 2025-04-01 and 2025-04-30? | `db.transactions.aggregate([{$match: {account_id: 39789, bucket_start_date: {$gte: new Date('2025-04-01'), $lte: new Date('2025-04-30')}}}, {$unwind: '$transactions'}, {$group: {_id: null, distinct_transaction_codes: {$addToSet: '$transactions.transaction_code'}}}])` | transactions |
| What are the top 5 symbols with the highest total transaction amount for account 28028 between 2025-05-01 and 2025-05-31? | `db.transactions.aggregate([{$match: {account_id: 28028, bucket_start_date: {$gte: new Date('2025-05-01'), $lte: new Date('2025-05-31')}}}, {$unwind: '$transactions'}, {$group: {_id: '$transactions.symbol', total_amount: {$sum: {$toInt: '$transactions.total'}}}}, {$sort: {total_amount: -1}}, {$limit: 5}])` | transactions |
| What is the maximum price of a trade for the ticker "AAPL"? | `db.trades.find({ticker: "AAPL"}, {price: 1}).sort({price: -1}).limit(1)` | trades |
| What is the average price of trades for the ticker "GOOGL" on March 1, 2023? | `db.trades.aggregate([{$match: {ticker: "GOOGL", time: {$gte: ISODate("2023-03-01"), $lt: ISODate("2023-03-02")}}}, {$group: {_id: null, avgPrice: {$avg: "$price"}}}])` | trades |
| Find trades with a system value of "ABC" and a lag greater than 10. | `db.trades.find({"details.system": "ABC", "details.lag": {$gt: 10}})` | trades |
| Find the trade with the maximum number of shares for the ticker "MSFT". | `db.trades.find({ticker: "MSFT"}, {shares: 1}).sort({shares: -1}).limit(1)` | trades |
| Find trades where the highest bid is greater than the lowest ask. | `db.trades.find({"details.bids.0": {$gt: "details.asks.$slice(-1)"}})` | trades |
| Find trades with the ticker "AMZN" and a price range between 100 and 200. | `db.trades.find({ticker: "AMZN", price: {$gte: 100, $lte: 200}})` | trades |
| Find trades where the sum of the first two bids is greater than the sum of the last two asks. | `db.trades.find({"$expr": {"$gt": [{"$sum": ["$details.bids.slice(0, 2)"]}, {"$sum": ["$details.asks.slice(-2)"]}]}})` | trades |
| Find trades with the ticket value "ABC456" and a lag value less than 5. | `db.trades.find({ticket: "ABC456", "details.lag": {$lt: 5}})` | trades |
| Find the trade with the maximum lag value for the system "XYZ". | `db.trades.find({"details.system": "XYZ"}, {"details.lag": 1}).sort({"details.lag": -1}).limit(1)` | trades |
| Find trades where the length of the asks array is greater than the length of the bids array. | `db.trades.find({"$expr": {"$gt": [{"$size": "$details.asks"}, {"$size": "$details.bids"}]}})` | trades |
| Find trades with the ticker "FB" and a time range between March 1, 2023 and March 14, 2023. | `db.trades.find({ticker: "FB", time: {$gte: ISODate("2023-03-01"), $lt: ISODate("2023-03-15")}})` | trades |
| Find trades where the sum of the bids array is greater than 1000. | `db.trades.find({"$expr": {"$gt": [{"$sum": "$details.bids"}, 1000]}})` | trades |
| Find trades with the system value "PQR" and sort them by price in ascending order. | `db.trades.find({"details.system": "PQR"}).sort({price: 1})` | trades |
| Find trades where the length of the bids array is equal to 5. | `db.trades.find({"$expr": {"$eq": [{"$size": "$details.bids"}, 5]}})` | trades |
| Find trades where the first bid is greater than the last ask. | `db.trades.find({"$expr": {"$gt": ["$details.bids.0", "$details.asks.$slice(-1)"]}})` | trades |
| Find trades with the system value "LMN" and a lag value between 10 and 20. | `db.trades.find({"details.system": "LMN", "details.lag": {$gte: 10, $lte: 20}})` | trades |
| Find trades where the sum of the asks array is less than 1000. | `db.trades.find({"$expr": {"$lt": [{"$sum": "$details.asks"}, 1000]}})` | trades |
| Find trades where the length of the asks array is equal to the length of the bids array. | `db.trades.find({"$expr": {"$eq": [{"$size": "$details.asks"}, {"$size": "$details.bids"}]}})` | trades |
| Find trades with the system value "GHI" and a time range between March 1, 2023 and March 15, 2023. | `db.trades.find({"details.system": "GHI", time: {$gte: ISODate("2023-03-01"), $lt: ISODate("2023-03-15")}})` | trades |
| Find trades where the sum of the bids array is less than the sum of the asks array. | `db.trades.find({"$expr": {"$lt": [{"$sum": "$details.bids"}, {"$sum": "$details.asks"}]}})` | trades |
| Find trades where the length of the bids array is greater than 4. | `db.trades.find({"$expr": {"$gt": [{"$size": "$details.bids"}, 4]}})` | trades |
| Find trades with the ticker "AMD" and the first bid greater than 100. | `db.trades.find({ticker: "AMD", "details.bids.0": {$gt: 100}})` | trades |
| Find trades with the system value "XYZ" and the last ask less than 90. | `db.trades.find({"details.system": "XYZ", "details.asks.$slice(-1)": {$lt: 90}})` | trades |
| Find the trade with the maximum lag value for the ticket "ABC123". | `db.trades.find({ticket: "ABC123"}, {"details.lag": 1}).sort({"details.lag": -1}).limit(1)` | trades |
| Find trades where the second bid is greater than the third ask. | `db.trades.find({"$expr": {"$gt": ["$details.bids.1", "$details.asks.2"]}})` | trades |
| Find trades with the ticker "CSCO" and shares between 500 and 1000. | `db.trades.find({ticker: "CSCO", shares: {$gte: 500, $lte: 1000}})` | trades |
| Find trades where the length of the asks array is 3. | `db.trades.find({"$expr": {"$eq": [{"$size": "$details.asks"}, 3]}})` | trades |
| Find trades where the sum of the asks array is greater than 1500. | `db.trades.find({"$expr": {"$gt": [{"$sum": "$details.asks"}, 1500]}})` | trades |
| Find trades with the system value "PQR" and the third bid greater than 200. | `db.trades.find({"details.system": "PQR", "details.bids.2": {$gt: 200}})` | trades |
| Find trades where the length of the bids array is less than 5. | `db.trades.find({"$expr": {"$lt": [{"$size": "$details.bids"}, 5]}})` | trades |
| Find trades with the ticket value "GHI789" and a lag value of 0. | `db.trades.find({ticket: "GHI789", "details.lag": 0})` | trades |
| Find trades where the second ask is greater than the sum of the first two bids. | `db.trades.find({"$expr": {"$gt": ["$details.asks.1", {"$sum": ["$details.bids.slice(0, 2)"]}]}})` | trades |
| Find trades where the length of the asks array is not equal to the length of the bids array. | `db.trades.find({"$expr": {"$ne": [{"$size": "$details.asks"}, {"$size": "$details.bids"}]}})` | trades |
| Find trades with the system value "LMN" and the first ask greater than the second bid. | `db.trades.find({"details.system": "LMN", "$expr": {"$gt": ["$details.asks.0", "$details.bids.1"]}})` | trades |
| Find the trade with the minimum lag value for the ticket "JKL456". | `db.trades.find({ticket: "JKL456"}, {"details.lag": 1}).sort({"details.lag": 1}).limit(1)` | trades |
| Find trades where the sum of the first three bids is less than 500. | `db.trades.find({"$expr": {"$lt": [{"$sum": ["$details.bids.slice(0, 3)"]}, 500]}})` | trades |
| Find trades where the first bid is equal to the last ask. | `db.trades.find({"$expr": {"$eq": ["$details.bids.0", "$details.asks.$slice(-1)"]}})` | trades |
| Find trades where the length of the bids array is 2. | `db.trades.find({"$expr": {"$eq": [{"$size": "$details.bids"}, 2]}})` | trades |
| Find trades with the system value "QRS" and the second ask less than 80. | `db.trades.find({"details.system": "QRS", "details.asks.1": {$lt: 80}})` | trades |
| Find trades where the sum of the asks array is between 1000 and 2000. | `db.trades.find({"$expr": {"$and": [{"$gte": [{"$sum": "$details.asks"}, 1000]}, {"$lte": [{"$sum": "$details.asks"}, 2000]}]}})` | trades |
| Find trades with the ticket value "PQR123" and the first bid greater than the second bid. | `db.trades.find({ticket: "PQR123", "$expr": {"$gt": ["$details.bids.0", "$details.bids.1"]}})` | trades |
| Find trades where the length of the asks array is greater than 5. | `db.trades.find({"$expr": {"$gt": [{"$size": "$details.asks"}, 5]}})` | trades |
| Find trades with the system value "TUV" and a lag value not equal to 10. | `db.trades.find({"details.system": "TUV", "details.lag": {$ne: 10}})` | trades |
| What is the average score of a student for the type "exam"? | db.students.aggregate([{ $unwind: "$scores" }, { $match: { "scores.type": "exam" } }, { $group: { _id: null, avgScore: { $avg: "$scores.score" } } }]) | students |
| Find the student with the highest score for the type "quiz". | db.students.aggregate([{ $unwind: "$scores" }, { $match: { "scores.type": "quiz" } }, { $sort: { "scores.score": -1 } }, { $limit: 1 }]) | students |
| Get the count of students who have scored above 80 in any type. | db.students.aggregate([{ $unwind: "$scores" }, { $match: { "scores.score": { $gt: 80 } } }, { $group: { _id: null, count: { $sum: 1 } } }]) | students |
| Find the names of students who have not scored in any type. | db.students.aggregate([{ $match: { $or: [{ scores: { $exists: false } }, { scores: { $size: 0 } }] } }, { $project: { name: 1 } }]) | students |
| Get the average score for each type across all students. | db.students.aggregate([{ $unwind: "$scores" }, { $group: { _id: "$scores.type", avgScore: { $avg: "$scores.score" } } }]) | students |
| Find the names of students who have scored above 90 in at least two types. | db.students.aggregate([{ $unwind: "$scores" }, { $match: { "scores.score": { $gt: 90 } } }, { $group: { _id: "$_id", scores: { $push: "$scores" } } }, { $match: { "scores.1": { $exists: true } } }, { $project: { name: 1 } }]) | students |
| Get the top 3 students with the highest total score across all types. | db.students.aggregate([{ $unwind: "$scores" }, { $group: { _id: "$_id", totalScore: { $sum: "$scores.score" } } }, { $sort: { totalScore: -1 } }, { $limit: 3 }]) | students |
| Find the names of students who have scored between 70 and 80 in the type "assignment". | db.students.aggregate([{ $unwind: "$scores" }, { $match: { "scores.type": "assignment", "scores.score": { $gte: 70, $lte: 80 } } }, { $project: { name: 1 } }]) | students |
| Get the count of students who have scored above 60 in at least two types. | db.students.aggregate([{ $unwind: "$scores" }, { $match: { "scores.score": { $gt: 60 } } }, { $group: { _id: "$_id", scores: { $push: "$scores" } } }, { $match: { "scores.1": { $exists: true } } }, { $group: { _id: null, count: { $sum: 1 } } }]) | students |
| Find the names of students who have scored in all types. | db.students.aggregate([{ $unwind: "$scores" }, { $group: { _id: "$_id", types: { $addToSet: "$scores.type" } } }, { $match: { types: { $setIsSubset: [["exam", "quiz", "assignment"]] } } }, { $project: { name: 1 } }]) | students |
| Get the maximum score for each student across all types. | db.students.aggregate([{ $unwind: "$scores" }, { $group: { _id: "$_id", maxScore: { $max: "$scores.score" } } }]) | students |
| Find the names of students who have scored below 50 in any type. | db.students.aggregate([{ $unwind: "$scores" }, { $match: { "scores.score": { $lt: 50 } } }, { $group: { _id: "$_id" } }, { $project: { name: 1 } }]) | students |
| Get the count of students who have scored above 80 in exactly two types. | db.students.aggregate([{ $unwind: "$scores" }, { $match: { "scores.score": { $gt: 80 } } }, { $group: { _id: "$_id", scores: { $push: "$scores" } } }, { $match: { "scores.1": { $exists: true }, "scores.2": { $exists: false } } }, { $group: { _id: null, count: { $sum: 1 } } }]) | students |
| Find the names of students who have scored the same in all types. | db.students.aggregate([{ $unwind: "$scores" }, { $group: { _id: { studentId: "$_id", score: "$scores.score" }, scores: { $push: "$scores" } } }, { $group: { _id: "$_id.studentId", uniqueScores: { $addToSet: "$_id.score" } } }, { $match: { uniqueScores: { $size: 1 } } }, { $project: { name: 1 } }]) | students |
| Get the average score for each student across all types. | db.students.aggregate([{ $unwind: "$scores" }, { $group: { _id: "$_id", avgScore: { $avg: "$scores.score" } } }]) | students |
| Find the names of students who have scored above 90 in at least one type. | db.students.aggregate([{ $unwind: "$scores" }, { $match: { "scores.score": { $gt: 90 } } }, { $group: { _id: "$_id" } }, { $project: { name: 1 } }]) | students |
| Get the count of students who have scored below 60 in all types. | db.students.aggregate([{ $unwind: "$scores" }, { $match: { "scores.score": { $lt: 60 } } }, { $group: { _id: "$_id", scores: { $push: "$scores" } } }, { $match: { scores: { $size: 3 } } }, { $group: { _id: null, count: { $sum: 1 } } }]) | students |
| Find the names of students who have scored in at least two types. | db.students.aggregate([{ $unwind: "$scores" }, { $group: { _id: "$_id", types: { $addToSet: "$scores.type" } } }, { $match: { types: { $size: { $gte: 2 } } } }, { $project: { name: 1 } }]) | students |
| Get the maximum score for each type across all students. | db.students.aggregate([{ $unwind: "$scores" }, { $group: { _id: "$scores.type", maxScore: { $max: "$scores.score" } } }]) | students |
| Find the names of students who have scored between 70 and 80 in at least two types. | db.students.aggregate([{ $unwind: "$scores" }, { $match: { "scores.score": { $gte: 70, $lte: 80 } } }, { $group: { _id: "$_id", scores: { $push: "$scores" } } }, { $match: { "scores.1": { $exists: true } } }, { $project: { name: 1 } }]) | students |
| Get the count of students who have scored above 90 in all types. | db.students.aggregate([{ $unwind: "$scores" }, { $match: { "scores.score": { $gt: 90 } } }, { $group: { _id: "$_id", scores: { $push: "$scores" } } }, { $match: { scores: { $size: 3 } } }, { $group: { _id: null, count: { $sum: 1 } } }]) | students |
| Find the names of students who have scored in only one type. | db.students.aggregate([{ $unwind: "$scores" }, { $group: { _id: "$_id", types: { $addToSet: "$scores.type" } } }, { $match: { types: { $size: 1 } } }, { $project: { name: 1 } }]) | students |
| Get the maximum difference between the highest and lowest score for each student across all types. | db.students.aggregate([{ $unwind: "$scores" }, { $group: { _id: "$_id", maxScore: { $max: "$scores.score" }, minScore: { $min: "$scores.score" } } }, { $project: { scoreDiff: { $subtract: ["$maxScore", "$minScore"] } } }]) | students |
| Find the names of students who have scored in all types and have an average score above 80. | db.students.aggregate([{ $unwind: "$scores" }, { $group: { _id: "$_id", avgScore: { $avg: "$scores.score" }, types: { $addToSet: "$scores.type" } } }, { $match: { avgScore: { $gt: 80 }, types: { $setIsSubset: [["exam", "quiz", "assignment"]] } } }, { $project: { name: 1 } }]) | students |
| Get the count of students who have scored below 60 in at least two types. | db.students.aggregate([{ $unwind: "$scores" }, { $match: { "scores.score": { $lt: 60 } } }, { $group: { _id: "$_id", scores: { $push: "$scores" } } }, { $match: { "scores.1": { $exists: true } } }, { $group: { _id: null, count: { $sum: 1 } } }]) | students |
| Find the names of students who have scored between 70 and 80 in all types. | db.students.aggregate([{ $unwind: "$scores" }, { $match: { "scores.score": { $gte: 70, $lte: 80 } } }, { $group: { _id: "$_id", scores: { $push: "$scores" } } }, { $match: { scores: { $size: 3 } } }, { $project: { name: 1 } }]) | students |
| Get the count of students who have scored above 90 in at least one type and below 60 in at least one type. | db.students.aggregate([{ $unwind: "$scores" }, { $group: { _id: "$_id", highScores: { $sum: { $cond: { if: { $gt: ["$scores.score", 90] }, then: 1, else: 0 } } }, lowScores: { $sum: { $cond: { if: { $lt: ["$scores.score", 60] }, then: 1, else: 0 } } } } }, { $match: { $and: [{ highScores: { $gt: 0 } }, { lowScores: { $gt: 0 } }] } }, { $group: { _id: null, count: { $sum: 1 } } }]) | students |
| Find the names of students who have scored above 85 in at least two types and below 60 in at most one type. | db.students.aggregate([{ $unwind: "$scores" }, { $group: { _id: "$_id", highScores: { $sum: { $cond: { if: { $gt: ["$scores.score", 85] }, then: 1, else: 0 } } }, lowScores: { $sum: { $cond: { if: { $lt: ["$scores.score", 60] }, then: 1, else: 0 } } }, scores: { $push: "$scores" } } }, { $match: { highScores: { $gte: 2 }, lowScores: { $lte: 1 } } }, { $project: { name: 1 } }]) | students |
| Get the names of students who have scored in all types and have a score difference of at least 30 between their highest and lowest scores. | db.students.aggregate([{ $unwind: "$scores" }, { $group: { _id: "$_id", maxScore: { $max: "$scores.score" }, minScore: { $min: "$scores.score" }, types: { $addToSet: "$scores.type" } } }, { $match: { $and: [{ types: { $setIsSubset: [["exam", "quiz", "assignment"]] } }, { $expr: { $gte: [{ $subtract: ["$maxScore", "$minScore"] }, 30] } }] } }, { $project: { name: 1 } }]) | students |
| Find the names of students who have scored above the average score in all types. | db.students.aggregate([{ $unwind: "$scores" }, { $group: { _id: { type: "$scores.type" }, avgScore: { $avg: "$scores.score" } } }, { $replaceRoot: { newRoot: { $mergeObjects: [{ $arrayElemAt: ["$_id", 0] }, { avgScore: "$avgScore" }] } } }, { $group: { _id: "$_id", avgScores: { $push: { type: "$type", avgScore: "$avgScore" } } } }, { $unwind: "$avgScores" }, { $group: { _id: "$_id._id", avgScoresByType: { $push: "$avgScores" } } }, { $unwind: "$scores" }, { $match: { "scores.score": { $gt: { $arrayElemAt: [{ $filter: { input: "$avgScoresByType", cond: { $eq: ["$$this.type", "$scores.type"] } } }, 0, "avgScore"] } } } }, { $group: { _id: "$_id", types: { $addToSet: "$scores.type" } } }, { $match: { types: { $setIsSubset: [["exam", "quiz", "assignment"]] } } }, { $project: { name: 1 } }]) | students |
| Get the count of students who have scored in all types and have a score difference of less than 10 between their highest and lowest scores. | db.students.aggregate([{ $unwind: "$scores" }, { $group: { _id: "$_id", maxScore: { $max: "$scores.score" }, minScore: { $min: "$scores.score" }, types: { $addToSet: "$scores.type" } } }, { $match: { $and: [{ types: { $setIsSubset: [["exam", "quiz", "assignment"]] } }, { $expr: { $lt: [{ $subtract: ["$maxScore", "$minScore"] }, 10] } }] } }, { $group: { _id: null, count: { $sum: 1 } } }]) | students |
| Find the names of students who have scored above 90 in exactly one type and below 60 in exactly one type. | db.students.aggregate([{ $unwind: "$scores" }, { $group: { _id: "$_id", highScores: { $sum: { $cond: { if: { $gt: ["$scores.score", 90] }, then: 1, else: 0 } } }, lowScores: { $sum: { $cond: { if: { $lt: ["$scores.score", 60] }, then: 1, else: 0 } } } } }, { $match: { $and: [{ highScores: 1 }, { lowScores: 1 }] } }, { $project: { name: 1 } }]) | students |
| Get the names of students who have scored within the top 3 for at least two types. | db.students.aggregate([{ $unwind: "$scores" }, { $sort: { "scores.score": -1 } }, { $group: { _id: { studentId: "$_id", type: "$scores.type" }, rank: { $dense: { partitionBy: "$scores.type", sortBy: { score: "$scores.score" } } } } }, { $match: { rank: { $lte: 3 } } }, { $group: { _id: "$_id.studentId", types: { $addToSet: "$_id.type" } } }, { $match: { types: { $size: { $gte: 2 } } } }, { $project: { name: 1 } }]) | students |
| Find the names of students who have scored the same in at least two types. | db.students.aggregate([{ $unwind: "$scores" }, { $group: { _id: { studentId: "$_id", score: "$scores.score" }, types: { $addToSet: "$scores.type" } } }, { $match: { types: { $size: { $gte: 2 } } } }, { $group: { _id: "$_id.studentId", sameScores: { $addToSet: { score: "$_id.score", types: "$types" } } } }, { $match: { sameScores: { $size: 1 } } }, { $project: { name: 1 } }]) | students |
| Get the count of students who have scored above the average in at least one type and below the average in at least one type. | db.students.aggregate([{ $unwind: "$scores" }, { $group: { _id: { type: "$scores.type" }, avgScore: { $avg: "$scores.score" } } }, { $replaceRoot: { newRoot: { $mergeObjects: [{ $arrayElemAt: ["$_id", 0] }, { avgScore: "$avgScore" }] } } }, { $group: { _id: "$_id", avgScores: { $push: { type: "$type", avgScore: "$avgScore" } } } }, { $unwind: "$avgScores" }, { $group: { _id: "$_id._id", avgScoresByType: { $push: "$avgScores" } } }, { $unwind: "$scores" }, { $group: { _id: "$_id", aboveAvg: { $sum: { $cond: { if: { $gt: ["$scores.score", { $arrayElemAt: [{ $filter: { input: "$avgScoresByType", cond: { $eq: ["$$this.type", "$scores.type"] } } }, 0, "avgScore"] }] }, then: 1, else: 0 } } }, belowAvg: { $sum: { $cond: { if: { $lt: ["$scores.score", { $arrayElemAt: [{ $filter: { input: "$avgScoresByType", cond: { $eq: ["$$this.type", "$scores.type"] } } }, 0, "avgScore"] }] }, then: 1, else: 0 } } } } }, { $match: { $and: [{ aboveAvg: { $gt: 0 } }, { belowAvg: { $gt: 0 } }] } }, { $group: { _id: null, count: { $sum: 1 } } }]) | students |
| Find the names of students who have scored within the top 5 for all types. | db.students.aggregate([{ $unwind: "$scores" }, { $sort: { "scores.score": -1 } }, { $group: { _id: { studentId: "$_id", type: "$scores.type" }, rank: { $dense: { partitionBy: "$scores.type", sortBy: { score: "$scores.score" } } } } }, { $match: { rank: { $lte: 5 } } }, { $group: { _id: "$_id.studentId", types: { $addToSet: "$_id.type" } } }, { $match: { types: { $setIsSubset: [["exam", "quiz", "assignment"]] } } }, { $project: { name: 1 } }]) | students |
| Get the names of students who have scored in all types and have a score difference of at most 20 between their highest and lowest scores. | db.students.aggregate([{ $unwind: "$scores" }, { $group: { _id: "$_id", maxScore: { $max: "$scores.score" }, minScore: { $min: "$scores.score" }, types: { $addToSet: "$scores.type" } } }, { $match: { $and: [{ types: { $setIsSubset: [["exam", "quiz", "assignment"]] } }, { $expr: { $lte: [{ $subtract: ["$maxScore", "$minScore"] }, 20] } }] } }, { $project: { name: 1 } }]) | students |
| Find the names of students who have scored above the average in all types and have a score difference of at least 20 between their highest and lowest scores. | db.students.aggregate([{ $unwind: "$scores" }, { $group: { _id: { type: "$scores.type" }, avgScore: { $avg: "$scores.score" } } }, { $replaceRoot: { newRoot: { $mergeObjects: [{ $arrayElemAt: ["$_id", 0] }, { avgScore: "$avgScore" }] } } }, { $group: { _id: "$_id", avgScores: { $push: { type: "$type", avgScore: "$avgScore" } } } }, { $unwind: "$avgScores" }, { $group: { _id: "$_id._id", avgScoresByType: { $push: "$avgScores" } } }, { $unwind: "$scores" }, { $match: { "scores.score": { $gt: { $arrayElemAt: [{ $filter: { input: "$avgScoresByType", cond: { $eq: ["$$this.type", "$scores.type"] } } }, 0, "avgScore"] } } } }, { $group: { _id: "$_id", maxScore: { $max: "$scores.score" }, minScore: { $min: "$scores.score" }, types: { $addToSet: "$scores.type" } } }, { $match: { $and: [{ types: { $setIsSubset: [["exam", "quiz", "assignment"]] } }, { $expr: { $gte: [{ $subtract: ["$maxScore", "$minScore"] }, 20] } }] } }, { $project: { name: 1 } }]) | students |
| Get the count of students who have scored above 90 in at least two types and below 60 in at most one type. | db.students.aggregate([{ $unwind: "$scores" }, { $group: { _id: "$_id", highScores: { $sum: { $cond: { if: { $gt: ["$scores.score", 90] }, then: 1, else: 0 } } }, lowScores: { $sum: { $cond: { if: { $lt: ["$scores.score", 60] }, then: 1, else: 0 } } } } }, { $match: { $and: [{ highScores: { $gte: 2 } }, { lowScores: { $lte: 1 } }] } }, { $group: { _id: null, count: { $sum: 1 } } }]) | students |
| Find the names of students who have scored the same in all types. | db.students.aggregate([{ $unwind: "$scores" }, { $group: { _id: { studentId: "$_id", score: "$scores.score" }, types: { $addToSet: "$scores.type" } } }, { $match: { types: { $setIsSubset: [["exam", "quiz", "assignment"]] } } }, { $group: { _id: "$_id.studentId", sameScores: { $addToSet: "$_id.score" } } }, { $match: { sameScores: { $size: 1 } } }, { $project: { name: 1 } }]) | students |
| Get the names of students who have scored in at least two types and have a score difference of at least 30 between their highest and lowest scores. | db.students.aggregate([{ $unwind: "$scores" }, { $group: { _id: "$_id", maxScore: { $max: "$scores.score" }, minScore: { $min: "$scores.score" }, types: { $addToSet: "$scores.type" } } }, { $match: { $and: [{ types: { $size: { $gte: 2 } } }, { $expr: { $gte: [{ $subtract: ["$maxScore", "$minScore"] }, 30] } }] } }, { $project: { name: 1 } }]) | students |
| Find the names of students who have scored within the top 3 for all types. | db.students.aggregate([{ $unwind: "$scores" }, { $sort: { "scores.score": -1 } }, { $group: { _id: { studentId: "$_id", type: "$scores.type" }, rank: { $dense: { partitionBy: "$scores.type", sortBy: { score: "$scores.score" } } } } }, { $match: { rank: { $lte: 3 } } }, { $group: { _id: "$_id.studentId", types: { $addToSet: "$_id.type" } } }, { $match: { types: { $setIsSubset: [["exam", "quiz", "assignment"]] } } }, { $project: { name: 1 } }]) | students |
| Get the count of students who have scored in all types and have a score difference of at most 10 between their highest and lowest scores. | db.students.aggregate([{ $unwind: "$scores" }, { $group: { _id: "$_id", maxScore: { $max: "$scores.score" }, minScore: { $min: "$scores.score" }, types: { $addToSet: "$scores.type" } } }, { $match: { $and: [{ types: { $setIsSubset: [["exam", "quiz", "assignment"]] } }, { $expr: { $lte: [{ $subtract: ["$maxScore", "$minScore"] }, 10] } }] } }, { $group: { _id: null, count: { $sum: 1 } } }]) | students |
| Retrieve the names of students who have achieved a score greater than 85 in precisely two types of assessments. | db.students.aggregate([{ $unwind: "$scores" }, { $match: { "scores.score": { $gt: 85 } } }, { $group: { _id: "$_id", scores: { $push: "$scores" } } }, { $match: { scores: { $size: 2 } } }, { $project: { name: 1 } }]) | students |
| Obtain a list of student names who have consistently scored below 60 across all assessment types. | db.students.aggregate([{ $unwind: "$scores" }, { $match: { "scores.score": { $lt: 60 } } }, { $group: { _id: "$_id", scores: { $push: "$scores" } } }, { $match: { scores: { $size: 3 } } }, { $project: { name: 1 } }]) | students |
| Identify the students who have achieved a top 3 ranking in precisely one assessment type. | db.students.aggregate([{ $unwind: "$scores" }, { $sort: { "scores.score": -1 } }, { $group: { _id: { studentId: "$_id", type: "$scores.type" }, rank: { $dense: { partitionBy: "$scores.type", sortBy: { score: "$scores.score" } } } } }, { $match: { rank: { $lte: 3 } } }, { $group: { _id: "$_id.studentId", types: { $addToSet: "$_id.type" } } }, { $match: { types: { $size: 1 } } }, { $project: { name: 1 } }]) | students |
| Retrieve the names of students who have not scored within the top 5 in any assessment type. | db.students.aggregate([{ $unwind: "$scores" }, { $sort: { "scores.score": -1 } }, { $group: { _id: { studentId: "$_id", type: "$scores.type" }, rank: { $dense: { partitionBy: "$scores.type", sortBy: { score: "$scores.score" } } } } }, { $match: { rank: { $gt: 5 } } }, { $group: { _id: "$_id.studentId" } }, { $project: { name: 1 } }]) | students |
| Determine the count of students who have achieved a score above 90 in at least one type and below 60 in at least one other type. | db.students.aggregate([{ $unwind: "$scores" }, { $group: { _id: "$_id", highScores: { $sum: { $cond: { if: { $gt: ["$scores.score", 90] }, then: 1, else: 0 } } }, lowScores: { $sum: { $cond: { if: { $lt: ["$scores.score", 60] }, then: 1, else: 0 } } } } }, { $match: { $and: [{ highScores: { $gt: 0 } }, { lowScores: { $gt: 0 } }] } }, { $group: { _id: null, count: { $sum: 1 } } }]) | students |
| Retrieve the names of students whose highest and lowest scores across all types differ by no more than 15 points. | db.students.aggregate([{ $unwind: "$scores" }, { $group: { _id: "$_id", maxScore: { $max: "$scores.score" }, minScore: { $min: "$scores.score" } } }, { $match: { $expr: { $lte: [{ $subtract: ["$maxScore", "$minScore"] }, 15] } } }, { $project: { name: 1 } }]) | students |
| Identify the students who have attained a score greater than the average in at least two types of assessments. | db.students.aggregate([{ $unwind: "$scores" }, { $group: { _id: { type: "$scores.type" }, avgScore: { $avg: "$scores.score" } } }, { $replaceRoot: { newRoot: { $mergeObjects: [{ $arrayElemAt: ["$_id", 0] }, { avgScore: "$avgScore" }] } } }, { $group: { _id: "$_id", avgScores: { $push: { type: "$type", avgScore: "$avgScore" } } } }, { $unwind: "$avgScores" }, { $group: { _id: "$_id._id", avgScoresByType: { $push: "$avgScores" } } }, { $unwind: "$scores" }, { $group: { _id: "$_id", aboveAvg: { $sum: { $cond: { if: { $gt: ["$scores.score", { $arrayElemAt: [{ $filter: { input: "$avgScoresByType", cond: { $eq: ["$$this.type", "$scores.type"] } } }, 0, "avgScore"] }] }, then: 1, else: 0 } } } } }, { $match: { aboveAvg: { $gte: 2 } } }, { $project: { name: 1 } }]) | students |
| Obtain the names of students who have consistently scored within the range of 70 to 80 across all assessment types. | db.students.aggregate([{ $unwind: "$scores" }, { $match: { "scores.score": { $gte: 70, $lte: 80 } } }, { $group: { _id: "$_id", scores: { $push: "$scores" } } }, { $match: { scores: { $size: 3 } } }, { $project: { name: 1 } }]) | students |
| Determine the count of students whose highest and lowest scores across all types are separated by at least 40 points. | db.students.aggregate([{ $unwind: "$scores" }, { $group: { _id: "$_id", maxScore: { $max: "$scores.score" }, minScore: { $min: "$scores.score" } } }, { $match: { $expr: { $gte: [{ $subtract: ["$maxScore", "$minScore"] }, 40] } } }, { $group: { _id: null, count: { $sum: 1 } } }]) | students |
| Retrieve the names of students who have scored within the top 3 rankings across all assessment types. | db.students.aggregate([{ $unwind: "$scores" }, { $sort: { "scores.score": -1 } }, { $group: { _id: { studentId: "$_id", type: "$scores.type" }, rank: { $dense: { partitionBy: "$scores.type", sortBy: { score: "$scores.score" } } } } }, { $match: { rank: { $lte: 3 } } }, { $group: { _id: "$_id.studentId", types: { $addToSet: "$_id.type" } } }, { $match: { types: { $setIsSubset: [["exam", "quiz", "assignment"]] } } }, { $project: { name: 1 } }]) | students |
| Obtain a list of student names who have achieved identical scores across all assessment types. | db.students.aggregate([{ $unwind: "$scores" }, { $group: { _id: { studentId: "$_id", score: "$scores.score" }, types: { $addToSet: "$scores.type" } } }, { $match: { types: { $setIsSubset: [["exam", "quiz", "assignment"]] } } }, { $group: { _id: "$_id.studentId", sameScores: { $addToSet: "$_id.score" } } }, { $match: { sameScores: { $size: 1 } } }, { $project: { name: 1 } }]) | students |
| Determine the count of students who have scored below 50 in at least two types of assessments. | db.students.aggregate([{ $unwind: "$scores" }, { $match: { "scores.score": { $lt: 50 } } }, { $group: { _id: "$_id", scores: { $push: "$scores" } } }, { $match: { scores: { $size: { $gte: 2 } } } }, { $group: { _id: null, count: { $sum: 1 } } }]) | students |
| Retrieve the names of students who have achieved a score greater than 90 in precisely one assessment type. | db.students.aggregate([{ $unwind: "$scores" }, { $match: { "scores.score": { $gt: 90 } } }, { $group: { _id: "$_id", scores: { $push: "$scores" } } }, { $match: { scores: { $size: 1 } } }, { $project: { name: 1 } }]) | students |
| Identify the students whose highest score across all types exceeds their lowest score by more than 50 points. | db.students.aggregate([{ $unwind: "$scores" }, { $group: { _id: "$_id", maxScore: { $max: "$scores.score" }, minScore: { $min: "$scores.score" } } }, { $match: { $expr: { $gt: [{ $subtract: ["$maxScore", "$minScore"] }, 50] } } }, { $project: { name: 1 } }]) | students |
| Obtain the names of students who have consistently scored above the average in all assessment types. | db.students.aggregate([{ $unwind: "$scores" }, { $group: { _id: { type: "$scores.type" }, avgScore: { $avg: "$scores.score" } } }, { $replaceRoot: { newRoot: { $mergeObjects: [{ $arrayElemAt: ["$_id", 0] }, { avgScore: "$avgScore" }] } } }, { $group: { _id: "$_id", avgScores: { $push: { type: "$type", avgScore: "$avgScore" } } } }, { $unwind: "$avgScores" }, { $group: { _id: "$_id._id", avgScoresByType: { $push: "$avgScores" } } }, { $unwind: "$scores" }, { $match: { "scores.score": { $gt: { $arrayElemAt: [{ $filter: { input: "$avgScoresByType", cond: { $eq: ["$$this.type", "$scores.type"] } } }, 0, "avgScore"] } } } }, { $group: { _id: "$_id", types: { $addToSet: "$scores.type" } } }, { $match: { types: { $setIsSubset: [["exam", "quiz", "assignment"]] } } }, { $project: { name: 1 } }]) | students |
| Determine the count of students who have scored within the top 5 rankings in at least two assessment types. | db.students.aggregate([{ $unwind: "$scores" }, { $sort: { "scores.score": -1 } }, { $group: { _id: { studentId: "$_id", type: "$scores.type" }, rank: { $dense: { partitionBy: "$scores.type", sortBy: { score: "$scores.score" } } } } }, { $match: { rank: { $lte: 5 } } }, { $group: { _id: "$_id.studentId", types: { $addToSet: "$_id.type" } } }, { $match: { types: { $size: { $gte: 2 } } } }, { $group: { _id: null, count: { $sum: 1 } } }]) | students |
| Retrieve the names of students who have achieved a score below 60 in precisely one assessment type. | db.students.aggregate([{ $unwind: "$scores" }, { $match: { "scores.score": { $lt: 60 } } }, { $group: { _id: "$_id", scores: { $push: "$scores" } } }, { $match: { scores: { $size: 1 } } }, { $project: { name: 1 } }]) | students |
| Identify the students who have scored identically in at least two types of assessments. | db.students.aggregate([{ $unwind: "$scores" }, { $group: { _id: { studentId: "$_id", score: "$scores.score" }, types: { $addToSet: "$scores.type" } } }, { $match: { types: { $size: { $gte: 2 } } } }, { $group: { _id: "$_id.studentId", sameScores: { $addToSet: { score: "$_id.score", types: "$types" } } } }, { $match: { sameScores: { $size: 1 } } }, { $project: { name: 1 } }]) | students |
| Obtain a list of student names who have scored above the average in precisely one assessment type. | db.students.aggregate([{ $unwind: "$scores" }, { $group: { _id: { type: "$scores.type" }, avgScore: { $avg: "$scores.score" } } }, { $replaceRoot: { newRoot: { $mergeObjects: [{ $arrayElemAt: ["$_id", 0] }, { avgScore: "$avgScore" }] } } }, { $group: { _id: "$_id", avgScores: { $push: { type: "$type", avgScore: "$avgScore" } } } }, { $unwind: "$avgScores" }, { $group: { _id: "$_id._id", avgScoresByType: { $push: "$avgScores" } } }, { $unwind: "$scores" }, { $group: { _id: "$_id", aboveAvg: { $sum: { $cond: { if: { $gt: ["$scores.score", { $arrayElemAt: [{ $filter: { input: "$avgScoresByType", cond: { $eq: ["$$this.type", "$scores.type"] } } }, 0, "avgScore"] }] }, then: 1, else: 0 } } } } }, { $match: { aboveAvg: 1 } }, { $project: { name: 1 } }]) | students |
| Determine the count of students who have scored below the average in at least two assessment types. | db.students.aggregate([{ $unwind: "$scores" }, { $group: { _id: { type: "$scores.type" }, avgScore: { $avg: "$scores.score" } } }, { $replaceRoot: { newRoot: { $mergeObjects: [{ $arrayElemAt: ["$_id", 0] }, { avgScore: "$avgScore" }] } } }, { $group: { _id: "$_id", avgScores: { $push: { type: "$type", avgScore: "$avgScore" } } } }, { $unwind: "$avgScores" }, { $group: { _id: "$_id._id", avgScoresByType: { $push: "$avgScores" } } }, { $unwind: "$scores" }, { $group: { _id: "$_id", belowAvg: { $sum: { $cond: { if: { $lt: ["$scores.score", { $arrayElemAt: [{ $filter: { input: "$avgScoresByType", cond: { $eq: ["$$this.type", "$scores.type"] } } }, 0, "avgScore"] }] }, then: 1, else: 0 } } } } }, { $match: { belowAvg: { $gte: 2 } } }, { $group: { _id: null, count: { $sum: 1 } } }]) | students |
| Retrieve all picture documents containing the keyword "sunset" in their tags array | db.images.find({"tags": "sunset"}) | images |
| Count the number of photo documents with the tag "portrait" | db.images.count({"tags": "portrait"}) | images |
| Find the image document with the largest width dimension | db.images.find().sort({"width": -1}).limit(1) | images |
| Retrieve the image document with the smallest height dimension | db.images.find().sort({"height": 1}).limit(1) | images |
| Fetch images where the width exceeds 1200 pixels | db.images.find({"width": {"$gt": 1200}}) | images |
| Get image documents with height less than or equal to 800 pixels | db.images.find({"height": {"$lte": 800}}) | images |
| Retrieve pictures that have both "nature" and "landscape" tags | db.images.find({"tags": {"$all": ["nature", "landscape"]}}) | images |
| Find images that are either tagged as "city" or "urban" | db.images.find({"$or": [{"tags": "city"}, {"tags": "urban"}]}) | images |
| Count the number of images with a width of exactly 1024 pixels | db.images.count({"width": 1024}) | images |
| Get the count of images where the height is between 600 and 900 pixels | db.images.count({"height": {"$gte": 600, "$lte": 900}}) | images |
| Retrieve pictures tagged as "beach" and having width greater than 1600 pixels | db.images.find({"tags": "beach", "width": {"$gt": 1600}}) | images |
| Find images tagged as "sunrise" with height less than 480 pixels | db.images.find({"tags": "sunrise", "height": {"$lt": 480}}) | images |
| Get images that have at least 4 tags in their tags array | db.images.find({"tags": {"$size": {"$gte": 4}}}) | images |
| Retrieve the distinct tags present across all image documents | db.images.distinct("tags") | images |
| Count the number of unique tags present across all images | db.images.distinct("tags").length | images |
| Find pictures with the exact set of tags ["nature", "outdoor", "landscape"] | db.images.find({"tags": {"$all": ["nature", "outdoor", "landscape"]}}) | images |
| Retrieve images that do not have the "portrait" tag | db.images.find({"tags": {"$ne": "portrait"}}) | images |
| Get pictures with width greater than 1440 and height greater than 1080 | db.images.find({"width": {"$gt": 1440}, "height": {"$gt": 1080}}) | images |
| Find images where either the width is less than 640 or the height is less than 480 | db.images.find({"$or": [{"width": {"$lt": 640}}, {"height": {"$lt": 480}}]}) | images |
| Calculate the average width across all image documents | db.images.aggregate([{"$group": {"_id": null, "avgWidth": {"$avg": "$width"}}}]) | images |
| Find the average height of images tagged as "landscape" | db.images.aggregate([{"$match": {"tags": "landscape"}}, {"$group": {"_id": null, "avgHeight": {"$avg": "$height"}}}]) | images |
| Retrieve the maximum width among images tagged as "portrait" | db.images.aggregate([{"$match": {"tags": "portrait"}}, {"$group": {"_id": null, "maxWidth": {"$max": "$width"}}}]) | images |
| Find the minimum height of images tagged as "cityscape" | db.images.aggregate([{"$match": {"tags": "cityscape"}}, {"$group": {"_id": null, "minHeight": {"$min": "$height"}}}]) | images |
| Count the number of images with width 1280 and height 720 | db.images.count({"width": 1280, "height": 720}) | images |
| Get the count of images with the tags "nature", "outdoor" | db.images.count({"tags": {"$all": ["nature", "outdoor"]}}) | images |
| Retrieve the distinct combinations of width and height across all images | db.images.distinct({"width": 1, "height": 1}) | images |
| Count the number of unique width-height combinations across all images | db.images.distinct({"width": 1, "height": 1}).length | images |
| Get the count of images with width 1920 and tagged as "landscape" | db.images.count({"width": 1920, "tags": "landscape"}) | images |
| Count the number of images with height 1080 and tags "nature", "outdoor" | db.images.count({"height": 1080, "tags": {"$all": ["nature", "outdoor"]}}) | images |
| What is the average time spent acquiring read locks for the client 'analytics' in milliseconds? | db.profiles.aggregate([{$unwind: "$lockStats.timeAcquiringMicros"}, {$group: {_id: "$client", avgReadLockTime: {$avg: {$cond: [{$eq: ["$client", "analytics"]}, "$lockStats.timeAcquiringMicros.r", 0]}}}}]) | profiles |
| Find the user who took the longest time to acquire a write lock. | db.profiles.aggregate([{$unwind: "$lockStats.timeAcquiringMicros"}, {$sort: {"lockStats.timeAcquiringMicros.w": -1}}, {$limit: 1}, {$project: {user: 1, writeAcquireTime: "$lockStats.timeAcquiringMicros.w"}}]) | profiles |
| Retrieve the top 10 clients with the highest number of key updates. | db.profiles.find({}, {client: 1, keyUpdates: 1}).sort({keyUpdates: -1}).limit(10) | profiles |
| What is the total time spent locked for read operations by the client 'reporting'? | db.profiles.aggregate([{$match: {client: "reporting"}}, {$unwind: "$lockStats.timeLockedMicros"}, {$group: {_id: null, totalReadLockedTime: {$sum: "$lockStats.timeLockedMicros.r"}}}]) | profiles |
| Calculate the average number of documents scanned for the namespace 'school.students'. | db.profiles.aggregate([{$match: {ns: "school.students"}}, {$group: {_id: null, avgDocsScanned: {$avg: "$nscanned"}}}]) | profiles |
| Retrieve the top 5 clients with the highest response lengths. | db.profiles.find({}, {client: 1, responseLength: 1}).sort({responseLength: -1}).limit(5) | profiles |
| Find the clients with the user 'admin' and operation type 'update'. | db.profiles.find({user: "admin", op: "update"}, {client: 1}) | profiles |
| What is the maximum time spent acquiring a write lock? | db.profiles.aggregate([{$unwind: "$lockStats.timeAcquiringMicros"}, {$group: {_id: null, maxWriteAcquireTime: {$max: "$lockStats.timeAcquiringMicros.w"}}}]) | profiles |
| Retrieve the clients with the namespace 'school.teachers' and operation type 'insert'. | db.profiles.find({ns: "school.teachers", op: "insert"}, {client: 1}) | profiles |
| What is the minimum time spent locked for write operations? | db.profiles.aggregate([{$unwind: "$lockStats.timeLockedMicros"}, {$group: {_id: null, minWriteLockedTime: {$min: "$lockStats.timeLockedMicros.w"}}}]) | profiles |
| Calculate the average number of documents skipped for the operation type 'findAndModify'. | db.profiles.aggregate([{$match: {op: "findAndModify"}}, {$group: {_id: null, avgDocsSkipped: {$avg: "$ntoskip"}}}]) | profiles |
| Find the clients with the user 'instructor' and response length between 1000 and 5000 bytes. | db.profiles.find({user: "instructor", responseLength: {$gte: 1000, $lte: 5000}}, {client: 1}) | profiles |
| What is the maximum time spent locked for read operations? | db.profiles.aggregate([{$unwind: "$lockStats.timeLockedMicros"}, {$group: {_id: null, maxReadLockedTime: {$max: "$lockStats.timeLockedMicros.r"}}}]) | profiles |
| Retrieve the clients with the operation type 'find' and number of documents returned between 100 and 500. | db.profiles.find({op: "find", nreturned: {$gte: 100, $lte: 500}}, {client: 1}) | profiles |
| Calculate the average number of documents scanned for the operation type 'update'. | db.profiles.aggregate([{$match: {op: "update"}}, {$group: {_id: null, avgDocsScanned: {$avg: "$nscanned"}}}]) | profiles |
| Find the clients with the user 'studentApp' and number of key updates between 10 and 100. | db.profiles.find({user: "studentApp", keyUpdates: {$gte: 10, $lte: 100}}, {client: 1}) | profiles |
| What is the minimum time spent acquiring a read lock? | db.profiles.aggregate([{$unwind: "$lockStats.timeAcquiringMicros"}, {$group: {_id: null, minReadAcquireTime: {$min: "$lockStats.timeAcquiringMicros.r"}}}]) | profiles |
| Retrieve the clients with the namespace 'school.grades' and number of documents returned between 100 and 500. | db.profiles.find({ns: "school.grades", nreturned: {$gte: 100, $lte: 500}}, {client: 1}) | profiles |
| Find the clients with the operation type 'getMore' and execution time between 1000 and 5000 milliseconds. | db.profiles.find({op: "getMore", millis: {$gte: 1000, $lte: 5000}}, {client: 1}) | profiles |
| What is the maximum number of key updates for a client? | db.profiles.aggregate([{$group: {_id: null, maxKeyUpdates: {$max: "$keyUpdates"}}}]) | profiles |
| Retrieve the clients with the user 'backgroundJob' and number of yield operations between 10 and 100. | db.profiles.find({user: "backgroundJob", numYield: {$gte: 10, $lte: 100}}, {client: 1}) | profiles |
| Calculate the average response length for the operation type 'aggregate'. | db.profiles.aggregate([{$match: {op: "aggregate"}}, {$group: {_id: null, avgResponseLength: {$avg: "$responseLength"}}}]) | profiles |
| What is the average time spent locked for write operations by the client 'bulkImporter'? | db.profiles.aggregate([{$match: {client: "bulkImporter"}}, {$unwind: "$lockStats.timeLockedMicros"}, {$group: {_id: null, avgWriteLockedTime: {$avg: "$lockStats.timeLockedMicros.w"}}}]) | profiles |
| Find the clients that executed queries on the namespace 'school.attendance'. | db.profiles.find({ns: "school.attendance"}, {client: 1}) | profiles |
| Retrieve the top 3 clients with the highest number of documents scanned. | db.profiles.find({}, {client: 1, nscanned: 1}).sort({nscanned: -1}).limit(3) | profiles |
| What is the total execution time for the client 'reportGenerator'? | db.profiles.aggregate([{$match: {client: "reportGenerator"}}, {$group: {_id: null, totalExecutionTime: {$sum: "$millis"}}}]) | profiles |
| Find the clients that performed write operations (insert, update, or delete) on the namespace 'school.fees'. | db.profiles.find({ns: "school.fees", op: {$in: ["insert", "update", "delete"]}}, {client: 1}) | profiles |
| Retrieve the clients with the user 'accountsManager' and operation type 'update'. | db.profiles.find({user: "accountsManager", op: "update"}, {client: 1}) | profiles |
| What is the maximum response length for the namespace 'school.staff'? | db.profiles.aggregate([{$match: {ns: "school.staff"}}, {$group: {_id: null, maxResponseLength: {$max: "$responseLength"}}}]) | profiles |
| Calculate the average number of yield operations for the operation type 'findAndModify'. | db.profiles.aggregate([{$match: {op: "findAndModify"}}, {$group: {_id: null, avgYieldOperations: {$avg: "$numYield"}}}]) | profiles |
| Retrieve the clients with the user 'libraryApp' and number of documents returned between 20 and 100. | db.profiles.find({user: "libraryApp", nreturned: {$gte: 20, $lte: 100}}, {client: 1}) | profiles |
| What is the minimum execution time for queries on the namespace 'school.timetable'? | db.profiles.aggregate([{$match: {ns: "school.timetable"}}, {$group: {_id: null, minExecutionTime: {$min: "$millis"}}}]) | profiles |
| Find the clients that executed write operations (insert, update, or delete) on the namespace 'school.transport'. | db.profiles.find({ns: "school.transport", op: {$in: ["insert", "update", "delete"]}}, {client: 1}) | profiles |
| Retrieve the clients with the user 'examCell' and operation type 'insert'. | db.profiles.find({user: "examCell", op: "insert"}, {client: 1}) | profiles |
| What is the maximum time spent acquiring a read lock for the namespace 'school.results'? | db.profiles.aggregate([{$match: {ns: "school.results"}}, {$unwind: "$lockStats.timeAcquiringMicros"}, {$group: {_id: null, maxReadAcquireTime: {$max: "$lockStats.timeAcquiringMicros.r"}}}]) | profiles |
| Find the clients that executed queries on the namespace 'school.events' and took more than 5 seconds to complete. | db.profiles.find({ns: "school.events", millis: {$gt: 5000}}, {client: 1}) | profiles |
| Retrieve the clients with the user 'parentApp' and operation type 'find'. | db.profiles.find({user: "parentApp", op: "find"}, {client: 1}) | profiles |
| What is the minimum number of key updates for the client 'dataImporter'? | db.profiles.aggregate([{$match: {client: "dataImporter"}}, {$group: {_id: null, minKeyUpdates: {$min: "$keyUpdates"}}}]) | profiles |
| Find the clients that executed queries on the namespace 'school.alumni' and scanned more than 10000 documents. | db.profiles.find({ns: "school.alumni", nscanned: {$gt: 10000}}, {client: 1}) | profiles |
| Retrieve the clients with the user 'hostelApp' and number of documents to return between 50 and 200. | db.profiles.find({user: "hostelApp", ntoreturn: {$gte: 50, $lte: 200}}, {client: 1}) | profiles |
| What is the maximum time spent locked for read operations for the namespace 'school.employees'? | db.profiles.aggregate([{$match: {ns: "school.employees"}}, {$unwind: "$lockStats.timeLockedMicros"}, {$group: {_id: null, maxReadLockedTime: {$max: "$lockStats.timeLockedMicros.r"}}}]) | profiles |
| Find the clients that executed queries on the namespace 'school.payroll' and returned more than 500 documents. | db.profiles.find({ns: "school.payroll", nreturned: {$gt: 500}}, {client: 1}) | profiles |
| Calculate the average number of documents to skip for queries with operation type 'find'. | db.profiles.aggregate([{$match: {op: "find"}}, {$group: {_id: null, avgDocsToSkip: {$avg: "$ntoskip"}}}]) | profiles |
| Retrieve the clients with the user 'admissionApp' and number of yield operations between 5 and 20. | db.profiles.find({user: "admissionApp", numYield: {$gte: 5, $lte: 20}}, {client: 1}) | profiles |
| What is the minimum response length for queries on the namespace 'school.resources'? | db.profiles.aggregate([{$match: {ns: "school.resources"}}, {$group: {_id: null, minResponseLength: {$min: "$responseLength"}}}]) | profiles |
| What are the names of countries that have more than one alternate spelling? | db.countries.find({ altSpellings: { $exists: true, $size: { $gt: 1 } } }, { name: 1, altSpellings: 1, _id: 0 }) | countries |
| Which countries have an area greater than 1,000,000 square kilometers? | db.countries.find({ area: { $gt: 1000000 } }, { name: 1, area: 1, _id: 0 }) | countries |
| Find the countries that share borders with the United States. | db.countries.find({ borders: "USA" }, { name: 1, borders: 1, _id: 0 }) | countries |
| List the countries with multiple calling codes. | db.countries.find({ callingCode: { $exists: true, $size: { $gt: 1 } } }, { name: 1, callingCode: 1, _id: 0 }) | countries |
| What is the capital city of France? | db.countries.findOne({ name: { common: "France" } }, { capital: 1, _id: 0 }) | countries |
| Get the list of landlocked countries in Europe. | db.countries.find({ landlocked: true, region: "Europe" }, { name: 1, landlocked: 1, region: 1, _id: 0 }) | countries |
| Which countries use the Euro as their currency? | db.countries.find({ currency: "EUR" }, { name: 1, currency: 1, _id: 0 }) | countries |
| Retrieve the demonym (name for residents) of Japan. | db.countries.findOne({ name: { common: "Japan" } }, { demonym: 1, _id: 0 }) | countries |
| List the countries in the Caribbean subregion. | db.countries.find({ subregion: "Caribbean" }, { name: 1, subregion: 1, _id: 0 }) | countries |
| What are the top-level domains (TLDs) used by Australia? | db.countries.findOne({ name: { common: "Australia" } }, { tld: 1, _id: 0 }) | countries |
| Get the names of countries that have "land" in their common name. | db.countries.find({ "name.common": { $regex: /land/, $options: 'i' } }, { name: 1, _id: 0 }) | countries |
| Find countries that have at least three borders. | db.countries.find({ borders: { $exists: true, $size: { $gte: 3 } } }, { name: 1, borders: 1, _id: 0 }) | countries |
| Retrieve the country details for the Czech Republic. | db.countries.findOne({ name: { common: "Czech Republic" } }, { _id: 0 }) | countries |
| List the countries in the Middle East region. | db.countries.find({ region: "Middle East" }, { name: 1, region: 1, _id: 0 }) | countries |
| Which countries have a calling code starting with "+1"? | db.countries.find({ callingCode: { $elemMatch: { $regex: /^\\+1/ } } }, { name: 1, callingCode: 1, _id: 0 }) | countries |
| Get the names of countries that have "Islands" in their alternate spellings. | db.countries.find({ altSpellings: { $regex: /Islands/i } }, { name: 1, altSpellings: 1, _id: 0 }) | countries |
| Find the country with the smallest area. | db.countries.find({}, { name: 1, area: 1, _id: 0 }).sort({ area: 1 }).limit(1) | countries |
| Retrieve the country details for the United Arab Emirates. | db.countries.findOne({ name: { common: "United Arab Emirates" } }, { _id: 0 }) | countries |
| Get the names of countries that have "Republic" in their common name. | db.countries.find({ "name.common": { $regex: /Republic/i } }, { name: 1, _id: 0 }) | countries |
| Find the countries that have a CIOC (International Olympic Committee) code starting with "C". | db.countries.find({ cioc: { $regex: /^C/ } }, { name: 1, cioc: 1, _id: 0 }) | countries |
| Retrieve the country details for Switzerland. | db.countries.findOne({ name: { common: "Switzerland" } }, { _id: 0 }) | countries |
| Which countries have a top-level domain containing ".gov"? | db.countries.find({ tld: { $regex: /\\.gov/ } }, { name: 1, tld: 1, _id: 0 }) | countries |
| Get the names of countries that have "United" in their common name. | db.countries.find({ "name.common": { $regex: /United/i } }, { name: 1, _id: 0 }) | countries |
| List the landlocked countries in South America. | db.countries.find({ landlocked: true, region: "South America" }, { name: 1, landlocked: 1, region: 1, _id: 0 }) | countries |
| Which countries have an ISO 3166-1 numeric code (ccn3) starting with "8"? | db.countries.find({ ccn3: { $regex: /^8/ } }, { name: 1, ccn3: 1, _id: 0 }) | countries |
| Get the names of countries that have "Kingdom" in their common name. | db.countries.find({ "name.common": { $regex: /Kingdom/i } }, { name: 1, _id: 0 }) | countries |
| List the countries that have "America" in their alternate spellings. | db.countries.find({ altSpellings: { $regex: /America/i } }, { name: 1, altSpellings: 1, _id: 0 }) | countries |
| Which countries have a top-level domain (TLD) containing ".co"? | db.countries.find({ tld: { $regex: /\\.co/ } }, { name: 1, tld: 1, _id: 0 }) | countries |
| Get the names of countries that have "Democratic" in their common name. | db.countries.find({ "name.common": { $regex: /Democratic/i } }, { name: 1, _id: 0 }) | countries |
| List the countries that have a calling code starting with "+7". | db.countries.find({ callingCode: { $elemMatch: { $regex: /^\\+7/ } } }, { name: 1, callingCode: 1, _id: 0 }) | countries |
| Which countries have "Republic" or "Federation" in their common name? | db.countries.find({ "name.common": { $regex: /Republic\|Federation/i } }, { name: 1, _id: 0 }) | countries |
| Get the names of countries that have "Saint" in their common name. | db.countries.find({ "name.common": { $regex: /Saint/i } }, { name: 1, _id: 0 }) | countries |
| List the countries that have a currency code "USD". | db.countries.find({ currency: "USD" }, { name: 1, currency: 1, _id: 0 }) | countries |
| Find the countries that have a CIOC (International Olympic Committee) code ending with "A". | db.countries.find({ cioc: { $regex: /A$/ } }, { name: 1, cioc: 1, _id: 0 }) | countries |
| Which countries have a top-level domain (TLD) containing ".uk"? | db.countries.find({ tld: { $regex: /\\.uk/ } }, { name: 1, tld: 1, _id: 0 }) | countries |
| Get the names of countries that have "Island" in their common name. | db.countries.find({ "name.common": { $regex: /Island/i } }, { name: 1, _id: 0 }) | countries |
| List the countries that have a calling code starting with "+60". | db.countries.find({ callingCode: { $elemMatch: { $regex: /^\\+60/ } } }, { name: 1, callingCode: 1, _id: 0 }) | countries |
| Which countries have "People's" in their common name? | db.countries.find({ "name.common": { $regex: /People's/i } }, { name: 1, _id: 0 }) | countries |
| Get the names of countries that have "City" in their common name. | db.countries.find({ "name.common": { $regex: /City/i } }, { name: 1, _id: 0 }) | countries |
| List the countries that have a currency code "GBP". | db.countries.find({ currency: "GBP" }, { name: 1, currency: 1, _id: 0 }) | countries |
| Find the countries that have a CIOC (International Olympic Committee) code starting with "Z". | db.countries.find({ cioc: { $regex: /^Z/ } }, { name: 1, cioc: 1, _id: 0 }) | countries |
| Which countries have a top-level domain (TLD) containing ".fr"? | db.countries.find({ tld: { $regex: /\\.fr/ } }, { name: 1, tld: 1, _id: 0 }) | countries |
| Get the names of countries that have "State" in their common name. | db.countries.find({ "name.common": { $regex: /State/i } }, { name: 1, _id: 0 }) | countries |
| List the countries that have a calling code starting with "+27". | db.countries.find({ callingCode: { $elemMatch: { $regex: /^\\+27/ } } }, { name: 1, callingCode: 1, _id: 0 }) | countries |
| List the countries that have a currency code "EUR". | db.countries.find({ currency: "EUR" }, { name: 1, currency: 1, _id: 0 }) | countries |
| Find the countries that have a CIOC (International Olympic Committee) code ending with "Z". | db.countries.find({ cioc: { $regex: /Z$/ } }, { name: 1, cioc: 1, _id: 0 }) | countries |
| List the titles of books published after January 1, 2010 | `db.books.find({"publishedDate": {$gt: new Date("2010-01-01")}}, {"title": 1, "_id": 0})` | books |
| Find books with the category "Fiction" | `db.books.find({"categories": "Fiction"})` | books |
| Get books with multiple authors | `db.books.find({"authors": {$size: {$gt: 1}}})` | books |
| Find books with a description longer than 500 characters | db.books.find({ $expr: { $gt: [{ $strLenCP: "$longDescription" }, 500] } }) | books |
| Find books with the category "Science Fiction" or "Fantasy" | `db.books.find({"categories": {$in: ["Science Fiction", "Fantasy"]}})` | books |
| List the titles of books with more than 3 categories | `db.books.find({"categories": {$size: {$gt: 3}}}, {"title": 1, "_id": 0})` | books |
| Find books with a thumbnail URL starting with "http://example.com" | `db.books.find({"thumbnailUrl": {$regex: /^http:\/\/example\.com/}})` | books |
| Get books with a page count between 200 and 400 | `db.books.find({"pageCount": {$gte: 200, $lte: 400}})` | books |
| List the titles and authors of books published in 2015 | `db.books.find({"publishedDate": {$gte: new Date("2015-01-01"), $lt: new Date("2016-01-01")}}, {"title": 1, "authors": 1, "_id": 0})` | books |
| Find books with the category "Non-Fiction" and a page count greater than 300 | `db.books.find({"categories": "Non-Fiction", "pageCount": {$gt: 300}})` | books |
| Get books with a short description containing the word "adventure" | `db.books.find({"shortDescription": {$regex: /adventure/i}})` | books |
| List the titles of books with at least one author starting with "J" | `db.books.find({"authors": {$regex: /^J/}}, {"title": 1, "_id": 0})` | books |
| Find books with the category "History" and published after 2010 | `db.books.find({"categories": "History", "publishedDate": {$gt: new Date("2010-01-01")}})` | books |
| Get books with a long description containing the phrase "New York Times" | `db.books.find({"longDescription": {$regex: /New York Times/}})` | books |
| List the titles and categories of books with a page count less than 150 | `db.books.find({"pageCount": {$lt: 150}}, {"title": 1, "categories": 1, "_id": 0})` | books |
| Find books with the author "Stephen King" and the category "Horror" | `db.books.find({"authors": "Stephen King", "categories": "Horror"})` | books |
| Get books with a thumbnail URL ending with ".jpg" | `db.books.find({"thumbnailUrl": {$regex: /\.jpg$/}})` | books |
| List the titles of books with the category "Biography" or "Autobiography" | `db.books.find({"categories": {$in: ["Biography", "Autobiography"]}}, {"title": 1, "_id": 0})` | books |
| Find books with the author "Jane Austen" and a page count between 300 and 500 | `db.books.find({"authors": "Jane Austen", "pageCount": {$gte: 300, $lte: 500}})` | books |
| Get books with a short description containing the word "mystery" | `db.books.find({"shortDescription": {$regex: /mystery/i}})` | books |
| List the titles and authors of books with the category "Travel" | `db.books.find({"categories": "Travel"}, {"title": 1, "authors": 1, "_id": 0})` | books |
| Find books with the author "J.R.R. Tolkien" and the category "Fantasy" | `db.books.find({"authors": "J.R.R. Tolkien", "categories": "Fantasy"})` | books |
| Get books with a long description containing the phrase "best-selling" | `db.books.find({"longDescription": {$regex: /best-selling/i}})` | books |
| List the titles of books with the category "Cooking" and a page count greater than 200 | `db.books.find({"categories": "Cooking", "pageCount": {$gt: 200}}, {"title": 1, "_id": 0})` | books |
| Get the title and authors of books with an ISBN exactly 13 characters long | db.books.find({ $expr: { $eq: [{ $strLenCP: "$isbn" }, 13] } }, { title: 1, authors: 1, _id: 0 }) | books |
| Update the long description of books with a short description longer than 150 characters to include "See more details" | db.books.updateMany({ $expr: { $gt: [{ $strLenCP: "$shortDescription" }, 150] } }, { $set: { longDescription: { $concat: ["$longDescription", " See more details."] } } }) | books |
| Find books with a thumbnail URL longer than 60 characters | db.books.find({ $expr: { $gt: [{ $strLenCP: "$thumbnailUrl" }, 60] } }) | books |
| Get the title and categories of books with an ISBN longer than 13 characters | db.books.find({ $expr: { $gt: [{ $strLenCP: "$isbn" }, 13] } }, { title: 1, categories: 1, _id: 0 }) | books |
| Get the count of books with an author name longer than 20 characters | db.books.aggregate([{ $unwind: "$authors" }, { $match: { $expr: { $gt: [{ $strLenCP: "$authors" }, 20] } } }, { $group: { _id: null, count: { $sum: 1 } } }]) | books |
| Find books with a title longer than 50 characters | db.books.find({ $expr: { $gt: [{ $strLenCP: "$title" }, 50] } }) | books |
| Get the title and authors of books with a short description between 100 and 200 characters | db.books.find({ $expr: { $and: [{ $gte: [{ $strLenCP: "$shortDescription" }, 100] }, { $lte: [{ $strLenCP: "$shortDescription" }, 200] }] } }, { title: 1, authors: 1, _id: 0 }) | books |
| Find books with a long description longer than 1000 characters | db.books.find({ $expr: { $gt: [{ $strLenCP: "$longDescription" }, 1000] } }) | books |
| Find all users whose city is 'New York' and country is 'USA'. | `db.users.find({"address.city": "New York", "address.country": "USA"})` | users |
| Get the count of users who have a Visa card. | `db.users.count({"card.card_type": "Visa"})` | users |
| List the first names and last names of unmarried users. | `db.users.find({"married_status": false}, {"first_name": 1, "last_name": 1, "_id": 0})` | users |
| Find users whose card balance is greater than $5000. | `db.users.find({"card.balance": {$gt: "5000"}})` | users |
| Retrieve users from the UK and their card details. | `db.users.find({"address.country": "UK"}, {"card": 1})` | users |
| Get the distinct currency codes used by users' cards. | `db.users.distinct("card.currency_code")` | users |
| Find users whose card number starts with '4' (indicating Visa). | `db.users.find({"card.card_number": /^4/})` | users |
| List the email addresses of female users from California. | `db.users.find({"gender": "female", "address.state": "California"}, {"email": 1, "_id": 0})` | users |
| Retrieve users with American Express cards and their full addresses. | `db.users.find({"card.card_type": "American Express"}, {"address": 1})` | users |
| Count the number of users from each country. | `db.users.aggregate([{"$group": {"_id": "$address.country", "count": {"$sum": 1}}}])` | users |
| Find users whose card balance is less than $1000 and their marital status. | `db.users.find({"card.balance": {$lt: "1000"}}, {"married_status": 1})` | users |
| Get users from Germany whose card type is Mastercard. | `db.users.find({"address.country": "Germany", "card.card_type": "Mastercard"})` | users |
| List the email addresses of married users with Visa cards. | `db.users.find({"married_status": true, "card.card_type": "Visa"}, {"email": 1, "_id": 0})` | users |
| Retrieve users from Australia with a card balance greater than $10000. | `db.users.find({"address.country": "Australia", "card.balance": {$gt: "10000"}})` | users |
| Count the number of users with each card type. | `db.users.aggregate([{"$group": {"_id": "$card.card_type", "count": {"$sum": 1}}}])` | users |
| Find users from India whose card number ends with '1234'. | `db.users.find({"address.country": "India", "card.card_number": /1234$/})` | users |
| List the full names of male users from Canada. | `db.users.find({"gender": "male", "address.country": "Canada"}, {"first_name": 1, "last_name": 1, "_id": 0})` | users |
| Retrieve users with a Mastercard and their city and state. | `db.users.find({"card.card_type": "Mastercard"}, {"address.city": 1, "address.state": 1})` | users |
| Get the count of single users from each country. | `db.users.aggregate([{"$match": {"married_status": false}}, {"$group": {"_id": "$address.country", "count": {"$sum": 1}}}])` | users |
| Find users whose card number contains '5678'. | `db.users.find({"card.card_number": /5678/})` | users |
| List the email addresses of users from Spain with a Discover card. | `db.users.find({"address.country": "Spain", "card.card_type": "Discover"}, {"email": 1, "_id": 0})` | users |
| Retrieve users from Mexico with a card balance between $2000 and $5000. | `db.users.find({"address.country": "Mexico", "card.balance": {$gte: "2000", $lte: "5000"}})` | users |
| Count the number of married users from each state in the USA. | `db.users.aggregate([{"$match": {"married_status": true, "address.country": "USA"}}, {"$group": {"_id": "$address.state", "count": {"$sum": 1}}}])` | users |
| Find users from Brazil whose card type is not Visa or Mastercard. | `db.users.find({"address.country": "Brazil", "card.card_type": {$nin: ["Visa", "Mastercard"]}})` | users |
| List the full names and card details of users from France. | `db.users.find({"address.country": "France"}, {"first_name": 1, "last_name": 1, "card": 1, "_id": 0})` | users |
| Retrieve users from Italy with a card balance greater than $3000 and their marital status. | `db.users.find({"address.country": "Italy", "card.balance": {$gt: "3000"}}, {"married_status": 1})` | users |
| Get the count of users with each combination of country and card type. | `db.users.aggregate([{"$group": {"_id": {"country": "$address.country", "card_type": "$card.card_type"}, "count": {"$sum": 1}}}])` | users |
| Find users from Japan whose city is 'Tokyo' and their card details. | `db.users.find({"address.country": "Japan", "address.city": "Tokyo"}, {"card": 1})` | users |
| Find users who are either from the USA or have a Visa card, and their card balance is greater than $2000. | `db.users.find({$or: [{"address.country": "USA"}, {"card.card_type": "Visa"}], "card.balance": {$gt: "2000"}})` | users |
| Get the total card balance of married users from each country, sorted by the total balance in descending order. | `db.users.aggregate([{$match: {"married_status": true}}, {$group: {"_id": "$address.country", "total_balance": {$sum: {$toDouble: "$card.balance"}}}}, {$sort: {"total_balance": -1}}])` | users |
| List the full names of users who have a Mastercard and live in either California or New York, along with their city and state. | `db.users.find({"card.card_type": "Mastercard", "address.state": {$in: ["California", "New York"]}}, {"first_name": 1, "last_name": 1, "address.city": 1, "address.state": 1, "_id": 0})` | users |
| Retrieve users whose card balance is in the top 10% for their country, along with their country, card type, and balance. | `db.users.aggregate([{$group: {"_id": {"country": "$address.country", "card_type": "$card.card_type"}, "balances": {$push: {$toDouble: "$card.balance"}}}}, {$unwind: "$balances"}, {$sort: {"balances": -1}}, {$group: {"_id": {"country": "$_id.country", "card_type": "$_id.card_type"}, "top_balances": {$push: "$balances"}, "_id": 0}}, {$project: {"top_balances": {$slice: ["$top_balances", {$trunc: {$multiply: [{$size: "$top_balances"}, 0.9]}}]}, "_id": 0}}, {$unwind: "$top_balances"}, {$replaceRoot: {newRoot: {$mergeObjects: [{$arrayElemAt: ["$top_balances", 0]}, {"country": "$_id.country", "card_type": "$_id.card_type"}]}}}, {$project: {"_id": 0, "country": 1, "card_type": 1, "balance": {$toString: "$value"}}}])` | users |
| Find users who are married and have a card balance greater than the average balance of users from their country, and return their full name, country, card type, and balance. | `db.users.aggregate([{$match: {"married_status": true}}, {$group: {"_id": {"country": "$address.country", "card_type": "$card.card_type"}, "avg_balance": {$avg: {$toDouble: "$card.balance"}}}}, {$replaceRoot: {newRoot: {$mergeObjects: [{$arrayElemAt: ["$avg_balance", 0]}, "$$ROOT"]}}}, {$match: {"card.balance": {$gt: {$toString: "$avg_balance"}}}}, {$project: {"first_name": 1, "last_name": 1, "address.country": 1, "card.card_type": 1, "card.balance": 1, "_id": 0}}])` | users |
| Get the count of users from each state in the USA, grouped by marital status and card type, sorted by state and marital status. | `db.users.aggregate([{$match: {"address.country": "USA"}}, {$group: {"_id": {"state": "$address.state", "married_status": "$married_status", "card_type": "$card.card_type"}, "count": {$sum: 1}}}, {$sort: {"_id.state": 1, "_id.married_status": 1, "_id.card_type": 1}}])` | users |
| Find users whose card balance is in the top 25% for their country and card type, and return their full name, country, card type, and balance sorted by balance in descending order. | `db.users.aggregate([{$group: {"_id": {"country": "$address.country", "card_type": "$card.card_type"}, "balances": {$push: {$toDouble: "$card.balance"}}}}, {$unwind: "$balances"}, {$sort: {"balances": -1}}, {$group: {"_id": {"country": "$_id.country", "card_type": "$_id.card_type"}, "top_balances": {$push: "$balances"}, "_id": 0}}, {$project: {"top_balances": {$slice: ["$top_balances", {$trunc: {$multiply: [{$size: "$top_balances"}, 0.75]}}]}, "_id": 0}}, {$unwind: "$top_balances"}, {$replaceRoot: {newRoot: {$mergeObjects: [{$arrayElemAt: ["$top_balances", 0]}, {"country": "$_id.country", "card_type": "$_id.card_type"}]}}}, {$sort: {"value": -1}}, {$project: {"first_name": 1, "last_name": 1, "country": "$address.country", "card_type": 1, "balance": {$toString: "$value"}, "_id": 0}}])` | users |
| Get the average card balance of married users from each country, but only include countries where the average balance is greater than $5000, and sort the results by the average balance in descending order. | `db.users.aggregate([{$match: {"married_status": true}}, {$group: {"_id": "$address.country", "avg_balance": {$avg: {$toDouble: "$card.balance"}}}}, {$match: {"avg_balance": {$gt: 5000}}}, {$sort: {"avg_balance": -1}}])` | users |
| Find users who have a card balance greater than the average balance of users from their city, and return their full name, city, state, card type, and balance sorted by city and then balance in descending order. | `db.users.aggregate([{$group: {"_id": {"city": "$address.city", "card_type": "$card.card_type"}, "avg_balance": {$avg: {$toDouble: "$card.balance"}}}}, {$replaceRoot: {newRoot: {$mergeObjects: [{$arrayElemAt: ["$avg_balance", 0]}, "$$ROOT"]}}}, {$match: {"card.balance": {$gt: {$toString: "$avg_balance"}}}}, {$project: {"first_name": 1, "last_name": 1, "address.city": 1, "address.state": 1, "card.card_type": 1, "card.balance": 1, "_id": 0}}, {$sort: {"address.city": 1, "card.balance": -1}}])` | users |
| Get the count of users from each combination of country, state, and card type, but only include combinations where the count is greater than 10, and sort the results by country, state, and then count in descending order. | `db.users.aggregate([{$group: {"_id": {"country": "$address.country", "state": "$address.state", "card_type": "$card.card_type"}, "count": {$sum: 1}}}, {$match: {"count": {$gt: 10}}}, {$sort: {"_id.country": 1, "_id.state": 1, "count": -1}}])` | users |
| Find users who are either from the UK or have a Mastercard, and their card balance is in the top 20% for their country and card type combination, and return their full name, country, card type, and balance sorted by country and then balance in descending order. | `db.users.aggregate([{$match: {$or: [{"address.country": "UK"}, {"card.card_type": "Mastercard"}]}}, {$group: {"_id": {"country": "$address.country", "card_type": "$card.card_type"}, "balances": {$push: {$toDouble: "$card.balance"}}}}, {$unwind: "$balances"}, {$sort: {"balances": -1}}, {$group: {"_id": {"country": "$_id.country", "card_type": "$_id.card_type"}, "top_balances": {$push: "$balances"}, "_id": 0}}, {$project: {"top_balances": {$slice: ["$top_balances", {$trunc: {$multiply: [{$size: "$top_balances"}, 0.8]}}]}, "_id": 0}}, {$unwind: "$top_balances"}, {$replaceRoot: {newRoot: {$mergeObjects: [{$arrayElemAt: ["$top_balances", 0]}, {"country": "$_id.country", "card_type": "$_id.card_type"}]}}}, {$sort: {"country": 1, "value": -1}}, {$project: {"first_name": 1, "last_name": 1, "country": "$address.country", "card_type": 1, "balance": {$toString: "$value"}, "_id": 0}}])` | users |
| Get the count of students who joined the school after January 1, 2020. | `db.children.countDocuments({school_joining_date: {$gt: new Date("2020-01-01")}})` | children |
| Find the students whose lucky number of their first friend is 7. | `db.children.find({"friends.luckyNumber": 7}, {first_name: 1, last_name: 1, _id: 0})` | children |
| Get the students whose date of birth is between January 1, 2010, and December 31, 2012. | `db.children.find({dob: {$gte: new Date("2010-01-01"), $lte: new Date("2012-12-31")}}, {first_name: 1, last_name: 1, dob: 1, _id: 0})` | children |
| Get the total count of unique lucky numbers among all friends. | `db.children.distinct("friends.luckyNumber").then(nums => nums.length)` | children |
| Find the students whose first friend's name contains the word "Smith". | `db.children.find({"friends.name": /Smith/}, {first_name: 1, last_name: 1, _id: 0})` | children |
| Retrieve the names of students who have "Paris" as their favorite city. | `db.children.find({favCity: "Paris"}, {first_name: 1, last_name: 1, _id: 0})` | children |
| Get the students who joined the school in 2022 and love mathematics. | `db.children.find({school_joining_date: {$gte: new Date("2022-01-01"), $lte: new Date("2022-12-31")}, loveMaths: true}, {first_name: 1, last_name: 1, _id: 0})` | children |
| Find the names of students whose last friend's lucky number is 13. | `db.children.find({"friends.luckyNumber": 13}, {first_name: 1, last_name: 1, _id: 0})` | children |
| Retrieve the students who have at least three favorite cities. | `db.children.find({favCity: {$size: 3}}, {first_name: 1, last_name: 1, favCity: 1, _id: 0})` | children |
| Get the students whose date of birth is in the year 2012. | `db.children.find({dob: {$gte: new Date("2012-01-01"), $lte: new Date("2012-12-31")}}, {first_name: 1, last_name: 1, dob: 1, _id: 0})` | children |
| Find the names of male students who don't love mathematics. | `db.children.find({gender: "male", loveMaths: false}, {first_name: 1, last_name: 1, _id: 0})` | children |
| Retrieve the students whose friend's name is "John" and lucky number is 5. | `db.children.find({"friends.name": "John", "friends.luckyNumber": 5}, {first_name: 1, last_name: 1, _id: 0})` | children |
| Get the total count of students who have at least one friend. | `db.children.countDocuments({friends: {$exists: true, $not: {$size: 0}}})` | children |
| Find the names of students whose favorite city is "London" or "Tokyo". | `db.children.find({favCity: {$in: ["London", "Tokyo"]}}, {first_name: 1, last_name: 1, _id: 0})` | children |
| Retrieve the students who joined the school before January 1, 2020, and are in class 5. | `db.children.find({school_joining_date: {$lt: new Date("2020-01-01")}, class: 5}, {first_name: 1, last_name: 1, class: 1, _id: 0})` | children |
| Get the students whose first friend's gender is male and lucky number is greater than 10. | `db.children.find({"friends.gender": "male", "friends.luckyNumber": {$gt: 10}}, {first_name: 1, last_name: 1, _id: 0})` | children |
| Find the names of students who have at least two favorite cities, and one of them is "Sydney". | `db.children.find({favCity: {$all: ["Sydney"]}, favCity: {$size: {$gte: 2}}}, {first_name: 1, last_name: 1, favCity: 1, _id: 0})` | children |
| Retrieve the students whose date of birth is in the month of June. | `db.children.find({dob: {$gte: new Date("2000-06-01"), $lte: new Date("2000-06-30")}}, {first_name: 1, last_name: 1, dob: 1, _id: 0})` | children |
| Get the names of female students who love mathematics and have a friend whose lucky number is 8. | `db.children.find({gender: "female", loveMaths: true, "friends.luckyNumber": 8}, {first_name: 1, last_name: 1, _id: 0})` | children |
| Find the students whose first friend's name starts with "S" and lucky number is less than 5. | `db.children.find({"friends.name": {$regex: /^S/}, "friends.luckyNumber": {$lt: 5}}, {first_name: 1, last_name: 1, _id: 0})` | children |
| Retrieve the students who joined the school in 2021 and have "Rome" as one of their favorite cities. | `db.children.find({school_joining_date: {$gte: new Date("2021-01-01"), $lte: new Date("2021-12-31")}, favCity: "Rome"}, {first_name: 1, last_name: 1, _id: 0})` | children |
| Get the students whose last friend's gender is female and lucky number is divisible by 3. | `db.children.find({"friends.gender": "female", "friends.luckyNumber": {$mod: [3, 0]}}, {first_name: 1, last_name: 1, _id: 0})` | children |
| Find the names of students who have at least two female friends and their lucky numbers are different. | `db.children.find({"friends.gender": {$all: ["female", "female"]}, $expr: {$isArray: {$map: {"input": "$friends.luckyNumber", "as": "num", "in": {$setIsSubset: [["$$num"], "$friends.luckyNumber"]}}}}}, {first_name: 1, last_name: 1, _id: 0})` | children |
| Get the students whose date of birth is in the first quarter of the year and they love mathematics. | `db.children.find({dob: {$gte: new Date(new Date().getFullYear() + "-01-01"), $lte: new Date(new Date().getFullYear() + "-03-31")}, loveMaths: true}, {first_name: 1, last_name: 1, dob: 1, _id: 0})` | children |
| Retrieve the students who have at least two favorite cities, one of which is "Paris" and the other is "Tokyo". | `db.children.find({favCity: {$all: ["Paris", "Tokyo"]}, favCity: {$size: {$gte: 2}}}, {first_name: 1, last_name: 1, favCity: 1, _id: 0})` | children |
| Find the students whose first friend's name starts with "J" and their lucky number is even. | `db.children.find({"friends.name": {$regex: /^J/}, "friends.luckyNumber": {$mod: [2, 0]}}, {first_name: 1, last_name: 1, _id: 0})` | children |
| Get the total count of students grouped by their class and gender. | `db.children.aggregate([{$group: {_id: {class: "$class", gender: "$gender"}, count: {$sum: 1}}}])` | children |
| Retrieve the students who have at least two friends, and the lucky numbers of their friends are all unique. | `db.children.find({friends: {$size: {$gte: 2}}, $expr: {$eq: [{$size: {$setUnion: "$friends.luckyNumber"}}, {$size: "$friends.luckyNumber"}]}}, {first_name: 1, last_name: 1, _id: 0})` | children |
| Find the names of students who have at least three favorite cities, and one of them is "London". | `db.children.find({favCity: {$all: ["London"]}, favCity: {$size: {$gte: 3}}}, {first_name: 1, last_name: 1, favCity: 1, _id: 0})` | children |
| Retrieve the students who have at least two friends, and their lucky numbers sum to a multiple of 5. | `db.children.find({friends: {$size: {$gte: 2}}, $expr: {$eq: [{$sum: "$friends.luckyNumber"}, {$mod: [{$sum: "$friends.luckyNumber"}, 5]}]}}, {first_name: 1, last_name: 1, _id: 0})` | children |
| Find the names of students who have at least two favorite cities, and one of them is "Paris" or "Rome". | `db.children.find({favCity: {$size: {$gte: 2}}, favCity: {$in: ["Paris", "Rome"]}}, {first_name: 1, last_name: 1, favCity: 1, _id: 0})` | children |
| Retrieve the students who have at least two friends, and their lucky numbers are in ascending order. | `db.children.find({friends: {$size: {$gte: 2}}, $expr: {$eq: [{$slice: ["$friends.luckyNumber", {$sort: 1}]}, {$setIntersection: ["$friends.luckyNumber", {$reverseArray: "$friends.luckyNumber"}]}]}}, {first_name: 1, last_name: 1, _id: 0})` | children |
| Find the names of students who have at least three favorite cities, and none of them is "Paris". | `db.children.find({favCity: {$size: {$gte: 3}}, favCity: {$not: {$in: ["Paris"]}}}, {first_name: 1, last_name: 1, favCity: 1, _id: 0})` | children |
| Find the names of students who have at least three favorite cities, and two of them are not "Paris" or "London". | `db.children.find({favCity: {$size: {$gte: 3}}, favCity: {$not: {$all: ["Paris", "London"]}}}, {first_name: 1, last_name: 1, favCity: 1, _id: 0})` | children |
| Retrieve the students who have at least two friends, and the sum of their lucky numbers is greater than 20. | `db.children.find({friends: {$size: {$gte: 2}}, $expr: {$gt: [{$sum: "$friends.luckyNumber"}, 20]}}, {first_name: 1, last_name: 1, _id: 0})` | children |