Join Chander Dhall for an in-depth discussion in this video Query with filter and projection, part of Cosmos DB: Import, Manipulate, Index and Query.
- [Instructor] Now that you've seen how we can pass in SQL parameters, let's work towards doing a real world query. In line number 57 as we know we're passing back the entire company and that's where we do a select star. What this means is if we had an entity like company that may have 300 or 400 properties, we're sending back all the properties. Whereas in real world we have to be very careful, we cannot send the entire payload as it is and that's where we use a technique called projection where we can take the properties that are required by the client and that's all we send back.
So if the client says, "I just want the name and the category code", we use projection to make that happen. At the same time we might not always need something like just c dot name equals name, we might actually need a combination of few different filters. It could be an and filter like saying I want c dot name equals the name that you passed in and category code equals x, y, z or it could also be a combination of an and or or filter. So let's look into filtering and projection.
So we're going to rewrite this in a different way, we're going to pass in the default options, and then we're going to have a SQL like syntax so in this case I'm going to say from c in my query and then I'm going to have my filter here which is my where class and it's going to say where name equals, and if I remember we have something called Flextor as one of the companies in the database.
And then we can have c dot category code and that could equal web, that's a very popular category code. Since this is an or clause we're going to get multiple records back and then we can say select new and in this case we can only choose the properties we need, so if you need the ID, we can say we need the ID. And if we need the name, we can say return name and maybe the category code but in this case it's a little redundant so we'll just keep it this way and then do a to list right here.
Now generally speaking, the queries are differed and it's a good idea to keep it differed but just to make things simple I'm going to keep using to list throughout the course but this is not a recommended practice. So now let's do f5, and you can see we got like 32 companies back and all we have is the ID and the name which is what we need for this particular request and as you notice we don't have the rest of the object.
So we do projection using select and where clause has our filter and we can write the same query in a different way and in this case we can use more like a lambda syntax rather than using a LINQ query syntax. And we can say var results equals, we can take the same exact query and the default options as it is except the difference is we're going to use dot where and now we're going to use lambda syntax, c happens to be a variable, we can use any variable we like and we're going to keep this filter the same as what we had earlier.
And then we're going to use select and inside select we can say select and in this case we can even change the select to whatever we like. So let's say we need name, we can say name equals c dot name and we can also say we need the category and we can call it just category, we don't have to call it category code. Now that's it. Now again, I like to do a to list here and hit f5.
We got the same exact 32 records back except in this case we have the category and we have the name and on purpose we did not choose to add the ID. Now I'll show you another way to do the same exact thing and this is similar to what we've been doing earlier which is writing the query in a string format. So once we have document query, we can have the collection uri and then say select c dot name as name, if that's what we want, and then we can also say select c dot category code as category we can say from companies c where c dot name equals Flextor or c dot category code equals web, and then we have the default options.
Now this could be done in one line or you could also do this in multiple lines, all you have to do is add a plus sign. So for example you have this, you can end it right here and then say plus and then start quote right here and that should work too so either way, it's whatever you like. And now we have the default options and then again, we're going to do to list so we can see everything in one line. And before we run this let's have a look at the query, oh there's a typo right there, once we save this we're going to do f5.
So we see we have the same exact result back with 32 items and we have the name and the category. So these are the three ways you could do filtering and projection at the same time. My preferred way is usually number two which is a lambda syntax, it's easy to understand at the same time, it is very like the LINQ syntax we've been using and we all are familiar with. Adding strings is usually not a very good idea in the code and if you end up taking this approach just because you might have a SQL background, I highly suggest adding this as part of your app settings and get the value from a config.
- Data import scenarios
- Creating a database
- Creating a partitioned collection
- Data manipulation
- Importing documents with a stored procedure
- User-defined functions
- Excluding indexing at a document level
- Range indexing on strings
- Querying with SQL parameters
- Range operations