Welcome everybody you're watching Mr Fugu data science. Today we're featuring Mongolite using
R-studio for our interface and it's going to feature the lookup operator. I need to distinguish
something for you. You need to pay attention if you're using this it only works if you're in the
same database as the collections you're working with. Secondly, if you have very large files do
not use the lookup operator or if you must do so consider indexing, creating references or using
something like a match. Now that I preface with that here's my socials hit me up. Also I have the
community tab for my page if you want to leave some comments for suggestions and videos or just
feel free in this video if you'd like to do that. All the code will be in the link in description
Shameless plug I have a buy me a coffee you could consider a channel membership or throwing a super
thanks. I'm sorry but I gotta do it. Here's the Imports we're going to use hopefully even though
these are a little outdated in the specs. If you have something newer I hope there's no
compatibility issues. I cannot guarantee that. After I create my connection to MongoDB here.
You have this option like for example if I was creating this database and wanted to query through
it. Like looking for collections or anything like that that you see here. But I kept getting some
issues when I was trying to run that. I listed my databases so I could show you what we're going
to work with today. If you've never used this before this is just giving some syntax which
can help. We create on the fly as you see here. This collection as well as database it will not
be populated until you throw something in. So if you try to query while you're making something on
the fly nothing will show up. I'm just letting you be aware of that if you've never done this before.
I use my terminal. I made a connection to mongod. Here's the collection that I'm throwing in and I'm
specifying this ID just for fun with this dummy data set. I capitalize the I because, it throws us
off for our IDs which are created within MongDB. We have some character types which are Pokemon
and then whatever their description is. No this is not the correct number for Pikachu or Mew or
Charizard or whatever right. I just made those up. So if you're a Die Hard Pokemon fan don't bash me
in the comments. It's just for fun. Here's the data that I threw on. This is very important here for
when we're doing the lookup operator. I'm going to show you why. When we do the aggregation. We have a
separate collection we're going to throw in to our new database which is this is called Poke_orders
and we're having an ID an item which is Pikachu and Indeedee which is similar to what
you saw for the character type above. We have a price and a quantity of the cards available
we're creating this on the Fly once again but I changed this variable name so when I'm doing
queries I don't confuse myself to understand what I'm looking for. I inserted those in and we're good
to go and here's what it looks like when we do the query. We do the lookup operation like this.
We have this dollar notation we do the aggregate and this single quotation is important versus this
double quotation. Ao think of this as like our key value pairs where I'm doing the double notation
and I have this lookup operator with the dollar sign. How do we exactly read this aggregation for
lookup? We're taking this which is the very very first collection we did this is going to be the
Poke characters that we're dealing with here. So we're taking the Poke characters and we're going
to look up the other collection Poke_orders and we're taking in our current local collection of
Poke characters the character type and from the Poke_orders we're going to look at the item to do
the comparison. And we're doing a left outer join. I should throw a picture up on the screen so you
understand what's going on. When we print this out it's going to give a new column called inventory
sales data. Which is going to store it as a list and that's what you see here. That's what we're
dealing with. Let's see if maybe I should zoom this in a little bit to help you out. Hopefully
it doesn't pixelate later. This looks kind of odd because you're thinking to yourself. Well okay. Now
I got this list. But now I don't have any variable names for this list which is kind of problematic.
I mentioned earlier about these right here for these NA values or this incomplete which is
very important. This is intentional because when you're doing a comparison for a lookup operation
you're going to do the comparison for your local, your local collection. The one you're starting with.
You're doing a collection against the from. Which is the foreign collection and if something is
not in there during that comparison you're going to end up with null values. You're also having a
backwards comparison where you're doing a foreign field comparing it with your local field and if
that doesn't work you'll have a null value. And that's what's going on here. So what do we exactly
work with? The character type which is Pikachu, Indeedee, Charizard, etc and comparing it to see if it was
available in the other collection. And that's what you see here. And because it was available in this
specific collection we're good to go. We print it out with all the information and this is the
referencing number that we have stored from MongoDB. This is an ID or an object ID representation. These
three hundreds are interesting because when you did the character type here. You had NA values
so those NA values are basically the same and now you're just printing out the ID numbers which are
associated with this. When you print it out and you actually look at what's inside of that inventory
sales list for each entry. You see that it indeed has the actual key value pairs. And that's what's going
on with your your key is the column name and then your values are the rows of information. Which is
perfect. So if you needed to parse that you have that ability. We can do a separate aggregation.
Where now we're looking at taking these data. But instead we want to flatten unnest your column
that we have. So let's zoom out a little bit and we import the library of Tidy R to do this. You
will reach a problem because you have columns for example that are repeating in information. Because
you have repeating information like this or like this you have an issue. And it will give an error
to you unless you handle it. And so that's what I'm doing here. Because one I want to have unique
names for the columns. Two I decided to have it all printed out. Even though I have repeating
information. So you see these rows these object ID's. These are the same thing for referencing. So
that's how I accounted for this. Let's look at one more example where we use a pipeline.
If we're using a pipeline that you see here. We're starting off doing this with a Let. But what is Let
doing for us? This is kind of interesting because think of it as allowing you to access an input
for a collection. Where the variables are defined inside of the Let. Which is allowing us to use it
as a reference. We're taking the ID from the Poke characters and you're using a single dollar sign.
This is important. We are then using the pipeline to execute for the collection on the join. We have
a match, we have an expression, and an and. Each of which need to have the dollar symbols. and I
have this equation where I'm saying let's look at our new variable we named Poke underscore for a
double dollar sign now. Which is your reference from the variable of the Let. And then this is
from the foreign field that we're getting from the other collection. Because we're allowing us
to distinguish the name since they were similar or the same in this case. Allowing for a proper
comparison and AS is allowing us to print out the new column called match ID's. The single dollar
sign for one collection variables, double dollar sign references for the Let statement. This is the
printout of the list that we had and here's all of the information. Let's look at another example real
quick that is adapted from online that I had. and it's all in the citations in the bottom. Just these
were adapted a little bit. Let's say we have this collection called post and here's the information
where we have a title, author, insta likes. And we have this one which is a collection called
Comments. Where they're both in the same database and you have post title, comment, and likes. Let's
do some kind of aggregation with a lookup. So we perform this lookup where we're taking this is
code from Mongodb. Not code from Mongolite. This is specific code for that if we're using just MongoDB
in the Shell or wherever we would do it something like this. We're looking from our local field for
the current collection of insta-post. Where we want to do a lookup up for the foreign collection of
comments and we're going to look at the insta likes and the title. And we're changing the
variable names to post likes and post title. We're using the same exact thing we did before. But this
has two operations where we're doing a greater then and we're doing the same thing with the
comparison of the likes. The first thing remember this is going to be the reference from here. Where
we're taking our insta likes. The next thing we're going to do is take our post title and make it as
a comparison with our foreign field. We're going to display everything as a new column of lists
called comments. The output that we're going to have is going to be something like this. Where we
have the original data from the left side that we were doing the comparison with. Which is our
local field and then we have a list of whatever prints out from the right side. The right side in
this case will be the comparison for the greater than where we're taking the like values. So we have
a like value from the local field is greater than the the like value for the foreign field.
And you're printing off everything that was in the foreign field. In this case on the other hand
you did not have the greater than problem and you had to end up with an empty list of values.
I need to mention this because it's extremely important. Potential issues with speed. I said at
the very beginning if you're going to do this and you want speed. Even without the lookup operator
always consider investigating indexes. Indexes are a great thing and consider the index for
example on the foreign field. Another good thing is you could look at profiling to track the speed
of your operations. You could print it out you can make graphs you could do whatever you want there's
some links in the bottom if you really die hard about getting into this stuff. Nested collections
are going to definitely screw you up when you're trying to get speed. The match operator should be
considered over the lookup operator. Because you're doing less comparisons stop all of this and read
it at your own leisure it's extremely important and it'll help you with stress and frustration
later. Believe me. The young lady who reached out to me from Spain. Thank you for giving me this
idea for this video I greatly appreciate it. You know who you are. The speed up options for your
MongDB life indexing, indexing, indexing. Consider storing sub queries in separate documents for
faster reads. Consider doing some referencing. If you're in a situation where let's say you're
saving an ID from one document into another. What use cases may help you? If you had a many-to-many
relationship it's pretty good to use referencing. But understand you're making more round-trip calls
and if you have documents where it's bigger than 16 megabytes in size. Maybe you should consider
doing a reference. If you have something that is updated and grows relative to other parts
of the document consider it. Embedding think about this if you have nested documents this can help
you with accessing the same items at once improve your read times. Another thing not all one-to-one
relationships could benefit from embedding into to a document. And one-to-many relationships are
pretty good for embedding, and what am I talking about embedding? Think of making a file since
you're already looking at the same file consider like making it nested is kind of what I'm talking
about. Since you're already going to look at a document consider throwing it into a collection
and storing everything all at once. That's kind of the point of that so you're making less round-trip
calls. And then if you have really large files you should absolutely consider replication and
sharding. Really consider that. Here's some basic examples real quick for indexing, adding index on
multiple fields, removing an index, removing all indices. This is notation that would be relative
towards R-studio. Because of this. Just pay attention to that. Pause the video if you would
like to go through these and understand what's going on. Last things to mention flattening list.
Like the unwind operator. But that's different than the un-nesting we did earlier which was on
a data frame. But that'll be it for this video so thank you. Please like, share and subscribe
and if you subscribe turn on that notification Bell. Hit me up on my my socials and remember
if you would like to help support the channel consider buying me a coffee, throwing a super
thanks or a channel membership it'd be greatly appreciated. And I'll get back to Patreon at some
time. Notable mention here thank you to all the members who have helped support the channel and
reached out and gave me ideas over the last two two and a half years to make videos. I appreciate
all of you thank you. Bye. data science data science data science computer programming
inside your head