Join Chander Dhall for an in-depth discussion in this video Range indexing on strings, part of Cosmos DB: Import, Manipulate, Index and Query.
- [Narrator] Now we just looked looked at an example where we were doing range queries, but the data was integer based and that's usually simple. Well when we need to do range queries on strings the syntax is just a little bit different. So let's look into that now. So we'll keep this data as it is except we're going to add certain properties to it. And we can say city is Irvine and then we have state which is California.
And we can copy the same thing and add this data. It wouldn't really matter where you add it. 'Cause it's still an object and the order doesn't matter. And now we can go change this. So we can try to keep the same state and maybe change the state a couple of times.
So this is Austin, Texas. And we can still do LA and California so we have some data like that and then keep San Jose in California. At the end of the day that doesn't matter, but little bit different data is good to have. And then maybe have Dallas. So maybe we can do couple of queries where we take something like California which is a state and order it by the state. So it chose this data, ordered completely by the state.
So it'll have four records from California and two from Texas. Also now let's look at the collection policy. So as you know we have the collection definition which has the ID of the collection ID which is good. And we are going to now define our indexing policy. Which in this case could be new indexing policy. And then I'm going to specify the policy which is going to be range index.
And it's going to have the data type as you notice. Data type dot string. 'Cause we are trying to do manipulations with the string here. And then we can say precision equals minus one. And I will add the indexing policy with included paths. And as you can see we can have an included path; in this case, it's the root path. So that's simple.
Slash star. Now we can also add a path in a little different way. As you can see we have indexing policy dot included paths dot add. Very similarly to what we just did, included path. However, we need the state and we need to do range indexes except it's going to be on a string. So notice we have a question mark here, and we can also define indexes here.
So I could say collection, off type index. It may not work, you might have to resolve this. So if you do control dot you can see you have using system dot collections dot object model. And then you can define your range index here. And we have data type we need to specify that. In this case it's going to be string. And we can use the same precision.
Minus one. Now this is the recommended way of defining your indexing policy. Now once we have the indexing policy, it becomes very simple for us to go to collection definitely and add our indexing policy that we just and defined. That's all we need and I want to make sure that we are creating the collection with this particular collection definitely which we did earlier. You can add your request options if you need to, but that's it, and as you already know, we have already added the data that we need to support these queries.
And now we can go and make some queries happen. And we can still leave delete document collection. Just to clean up everything later. In order to make a query, what I really need is client dot create document query. And it needs the collection self link.
And then the actual query which could be select star from root R. Where R dot state is let's just say greater than equal to even D in this case, just because it's alphabetical and it should understand how to do that query. Now the return type in this case. So we will get back results. It's going to be more than one so we've got multiple results here.
So now before we run this code, I just want to make sure that we understand that this particular line of code is the right way to do it if we're going to specify a range index on string for all properties, and that's a good way of doing the policy. What we want to make sure is that we just go with the default policy in this case. So we're going to come in this code and just say we have a new indexing policy so we can do this: what we want to do is we want to include all the paths and only for anything which is underneath state we want to have a range index which is of type string.
Just to show you that we can do it at that particular level too. You don't have to enable this for every single property inside that particular object. So this is good and we have a break point right here so we're going to do F5. And as you can see we got here the results, but we're going to have to manual ride through these results to see the actual results. So you can see the first result is state Texas, employee name is Ben. We can do F10 again.
You can see the second result is also Texas. We should not get a third result and that's exactly what happened. As you can see we got everything in Texas. The reason is simple, we queried for state which is greater than D, and the other state which had four records was California and it starts with a C. So this is exactly what we were expecting. So we were able to show you a range query on a specified path.
- 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