1
00:00:02,120 --> 00:00:07,460
So we're coming closer to the end of this array section but it's really important that you understand

2
00:00:07,460 --> 00:00:13,400
how you can work with these different array stages like unwind and operators that you can then use within

3
00:00:13,400 --> 00:00:17,010
the project phase or the group phase.

4
00:00:17,050 --> 00:00:26,320
So let's say we now wanted to transform our friend objects such that we only output the highest exam score

5
00:00:26,590 --> 00:00:28,370
for every person,

6
00:00:28,420 --> 00:00:35,350
so we no longer have the exam scores array, we still only get three person results but we have no exam

7
00:00:35,350 --> 00:00:39,030
scores in there but only the highest exam score.

8
00:00:39,090 --> 00:00:42,050
Now there probably are multiple ways of achieving this,

9
00:00:42,100 --> 00:00:44,850
definitely feel free to pause the video and try it on your own,

10
00:00:44,930 --> 00:00:50,710
thereafter I will show you one solution I came up with.

11
00:00:50,830 --> 00:00:52,450
So were are you successful?

12
00:00:52,450 --> 00:00:54,780
This is definitely a challenging task.

13
00:00:55,090 --> 00:01:04,600
Now to get the highest possible score, what we need to do is we will need to unwind this array to get multiple

14
00:01:04,600 --> 00:01:12,280
documents per person with the scores being a top level element, so that we can then sort the documents

15
00:01:12,490 --> 00:01:20,080
by the scores and then group them together by person and take the first score that we find for that

16
00:01:20,080 --> 00:01:21,210
person.

17
00:01:21,280 --> 00:01:22,950
So let's do that step by step.

18
00:01:22,960 --> 00:01:26,750
The first phase I'll add here is the unwind phase

19
00:01:27,130 --> 00:01:31,960
and I want to unwind on the exam scores, like this.

20
00:01:31,960 --> 00:01:38,790
Now this will give me many new documents where we have many pairs of persons and their scores.

21
00:01:40,330 --> 00:01:49,800
In the next stage thereafter, I want to sort and I want to sort on exam scores which will not be an

22
00:01:49,800 --> 00:01:53,550
array anymore at this point because of unwind but a single value

23
00:01:53,730 --> 00:01:59,850
and I want to sort in descending order. By the way, you don't use $examScores here because

24
00:01:59,850 --> 00:02:03,340
I'm referring to the field name and not to the value of the field here,

25
00:02:04,020 --> 00:02:07,550
so let's try this.

26
00:02:07,560 --> 00:02:16,830
So now what I get is a bunch of documents and document that the top has the lowest score because what

27
00:02:16,830 --> 00:02:17,770
went wrong?

28
00:02:18,060 --> 00:02:26,730
Well I basically forgot that in exam scores, we have nested objects of course and we don't have the value

29
00:02:26,730 --> 00:02:29,200
itself stored in exam score after unwinding

30
00:02:29,280 --> 00:02:31,190
but the embedded document,

31
00:02:31,590 --> 00:02:33,220
so what can we do?

32
00:02:33,630 --> 00:02:44,720
Well of course we can sort by examScores.score here or let's try alternative, what if we unwind

33
00:02:45,080 --> 00:02:47,500
on exam scores score here?

34
00:02:49,200 --> 00:02:52,840
Well this does not work because the score is not an array,

35
00:02:52,860 --> 00:02:55,090
so this is not something we can do.

36
00:02:55,650 --> 00:03:03,270
We can of course do what I just mentioned and sort on a different field or we add a projection stage in

37
00:03:03,270 --> 00:03:12,180
between where we maybe omit the ID, where we do include the name and age and let's say also, let's say

38
00:03:12,180 --> 00:03:25,070
we leave out the hobbies and I do add a score field here which is exam scores score with a dollar sign

39
00:03:25,130 --> 00:03:25,340
here

40
00:03:25,360 --> 00:03:31,990
so that it take the value of this field. And then here I could sort by score which is now the newly stored

41
00:03:32,050 --> 00:03:32,860
value.

42
00:03:33,280 --> 00:03:35,690
If I take this and I insert it here,

43
00:03:35,710 --> 00:03:37,540
now this looks better,

44
00:03:37,570 --> 00:03:45,330
now we got our unwinded documents and you see that we have sorted them with the highest scores first.

45
00:03:46,900 --> 00:03:49,340
Now we just need to add a group stage,

46
00:03:49,420 --> 00:04:00,120
so what we can do here is we can add group and I want to group by my name of course so that the persons

47
00:04:00,210 --> 00:04:04,680
stay together, we could also group by ID if we kept this included.

48
00:04:04,830 --> 00:04:07,560
Maybe let's do that because names could be duplicated,

49
00:04:07,560 --> 00:04:08,710
IDs won't,

50
00:04:09,000 --> 00:04:12,980
so let's group by _id.

51
00:04:13,020 --> 00:04:15,740
Now I'm using a slightly different syntax than before,

52
00:04:15,900 --> 00:04:20,100
I don't have curly braces Id, like this,

53
00:04:20,100 --> 00:04:25,040
I could absolutely do that but this just adds some extra readability,

54
00:04:25,140 --> 00:04:31,500
in this case since I have the old ID and I keep it as an ID, I think it's readable enough.

55
00:04:31,500 --> 00:04:36,940
So I'm now grouping by ID and if I now copy that, let's see what that gives us,

56
00:04:37,880 --> 00:04:44,070
well it just give us the ID like this, we should use a value here.

57
00:04:46,230 --> 00:04:48,740
So now I just get three objects with the ID,

58
00:04:48,810 --> 00:04:54,120
the other fields are lost which makes sense because grouping well merges the documents together,

59
00:04:54,120 --> 00:04:58,320
so if we want to keep some information, we have to add it in the group stage.

60
00:04:58,770 --> 00:05:03,010
So let's go to the group stage here and there,

61
00:05:03,510 --> 00:05:05,760
I'll add a max score field

62
00:05:06,150 --> 00:05:13,230
and here, we can use the max operator which you can find in the official docs and I'll simply say that

63
00:05:13,320 --> 00:05:20,140
I want to get the maximum based on the score field.

64
00:05:20,140 --> 00:05:23,730
Now let's grab this and execute it

65
00:05:24,480 --> 00:05:27,420
and now you see that I calculate max scores.

66
00:05:27,420 --> 00:05:34,060
Now let's quickly validate if this is correct by checking out all friends here.

67
00:05:34,080 --> 00:05:41,060
So what we have there is that for the first person, Max, the highest score is 88.5

68
00:05:41,110 --> 00:05:43,370
and let's compare IDs,

69
00:05:43,390 --> 00:05:43,900
yes,

70
00:05:43,960 --> 00:05:44,920
this is the ID of Max,

71
00:05:44,910 --> 00:05:46,940
this seems to be correct here.

72
00:05:47,170 --> 00:05:53,030
Now for Manuel, the ID ends with a five, the highest score is 74.3

73
00:05:54,520 --> 00:05:55,590
and this looks good too

74
00:05:55,720 --> 00:05:58,650
and I assume it's also correct for Maria.

75
00:05:58,720 --> 00:05:59,830
So this worked,

76
00:05:59,860 --> 00:06:03,350
we now have our max score.

77
00:06:03,520 --> 00:06:06,590
Now it would also be nice to see the name here,

78
00:06:06,640 --> 00:06:07,130
right

79
00:06:07,300 --> 00:06:14,800
and it would also be nice to not just see the max score like this but to also sort these documents here

80
00:06:15,040 --> 00:06:18,780
in descending order.

81
00:06:18,800 --> 00:06:25,610
Now regarding the name, I'll add a name field here and there's another operator we can use here,

82
00:06:25,790 --> 00:06:28,920
it's the first operator which simply tells mongodb

83
00:06:28,950 --> 00:06:31,360
hey use the first value you encounter

84
00:06:31,490 --> 00:06:36,140
and since the name is the same on all documents for this group, this is perfectly fine.

85
00:06:36,140 --> 00:06:43,300
So I can point at $name here, referring to the name which I had in the input documents

86
00:06:43,590 --> 00:06:46,370
and now to also sort this,

87
00:06:46,460 --> 00:06:50,930
let's add another sort stage here and there,

88
00:06:50,930 --> 00:06:54,880
I will sort for my max score,

89
00:06:55,180 --> 00:07:00,220
so let's sort max score in a descending order.

90
00:07:00,220 --> 00:07:02,740
And now this is my finished pipeline,

91
00:07:02,770 --> 00:07:04,140
let's try it out.

92
00:07:05,960 --> 00:07:07,150
This is looking good,

93
00:07:07,160 --> 00:07:12,550
we have the name in there and we have descending scores.

94
00:07:12,650 --> 00:07:19,840
So this shows you hopefully how you can work with arrays and how you can combine the different pipeline

95
00:07:19,850 --> 00:07:28,750
phases to split arrays up, to pull array values out of there, to then group them together and so on.