Join Chander Dhall for an in-depth discussion in this video Range operations, part of Cosmos DB: Import, Manipulate, Index and Query.
- [Instructor] Now that we've done this, I'd like to show you how we can do range operations on numbers, as well as strings. Now, as you notice, there's not a lot of data inside the Company object, as it's a very simplistic object. So, a better entity that we can work with could be the Company object inside the test collection, which has a lot more properties. So, for that, we can go to our database and we can go to the Query Explorer and say select * from root r or I can also call it Companies c and then Run Query.
And there's a lot of data here. Now we can see that we also have some numbers. So, for example, number_of_employees is 60. So, what we could do is something like where c.number_of_employees is less than 100. So, we run the query. You'll see we got some data back and it's quite a few records. So, what we like to do is do this programmatically.
And first thing we need to change is the name of the collection. In this case, it's going to be Test. And then, go to our first query and see what we can do here. So, we have c.Name and Category_Code. We're going to go ahead and remove that. And it's going to be not of the type Company. And we're going to grab this particular property and then, since Test actually has Company's data, all we need to do in this case is just add one more property, which happens to be number_of_employees and change this to an integer.
Go back right here, in my demo. This is the code that needs to change and this is going to be a regular range query, where it's NumberOfEmployees is less than 100. We can also do and c.NumberOfEmployees, let's say, greater than 10. And we can do the same exact logic right here and here.
And then make sure that we change this and to this AND and that's it. We're going to put a break point right here, F5, and it's 2812 records. That means there's about 2812 companies that have employees that are between 10 and 100, not including 10 and 100, because we don't have an equal to sign with the less than and greater than. So, somewhere between 11 to 99 employees, we have about 2812 companies that employ 11 to 99 employees.
We're going to do another F10. You'll notice that this query's a little faster, because the same exact query so we're getting a cached result here. You can see the same exact number, which is 2812. Do another F10 and we have the same exact numbers. So, we were able to make all these three work, with our new filter, which happens to be a combination of two different things. Now, this is range query, when it comes to numbers, but range queries can also work on strings and, for that, let's look into our data.
Going back to the results, we've got something like Digg. Back to Visual Studio, we can go to Program.cs and we can change this particular value and say here c.Name.CompareTo and, in this case, we can compare this thing to that particular value and if that is greater than zero, then return all of those or, in this case, it might be better to go less than zero. So, we get a little smaller set of results and we can do the same exact thing in other different queries we have here.
This will be a very similar change. We're going to go ahead and comment this and we can go back here and change this. Move that range. Keep in mind you could also do a combination of a number range at the same time, a query with string range comparisons. Let's kill the last line. I'm going to change this to something else. Keep in mind you can also do a query with a combination of range operations on numbers, as well as strings, but, to keep things simple, we're going to do c.name and we can say greater than, let's say, Digg, in this case.
Now, this is just a syntax, but we will get an error, if we run this, because our options have not added any range filtering and, by default, since it's a hash index, it doesn't really have that, but if you were to refer the course on indexing, you can add the option to do range operations on strings and you should be okay with that particular query and it should work for you.
- 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