Introduction to CouchDB with .NET part 18: Mango indexes and queriescontinued

June 23, 2017 Leave a comment

Introduction

In theprevious post we started discussing a new feature in CouchDB 2.0, namely Mango queries. Mango queries and Mango indexes are also based on views but these views are created for us, we don’t need to worry about them. Therefore Mango queries provide us with a tool to perform ad-hoc searches in CouchDB with a JSON-based query language. We spent most of the previous post on setting up a small database of ZIP codes that we use for our demos. The gateway to performing the queries is the POST /_find endpoint in the HTTP API. We attach the query to the HTTP request body. We looked at the various properties of the query where the selector is the most important. We also saw that the _id property is indexed by default and then carried out our first query based on the id.

In this post we’ll continue looking at Mango queries.

Indexes and our first operator demo

We can insert various operators into the query to describe the selector we want. The following query uses the $lt operator which stands for less-than. We want to select all ZIP codes whose value is less than 40000:

POST http://localhost:5984/zipcodes/_find

{
"selector": {
"post_code": {"$lt": 40000}
}
}

The selector condition is itself a JSON object with the operator and its value. Operators and their values come in pairs like that.

Execute the query and…

…we get a warning along with the results:

{
"warning": "no matching index found, create an index to optimize query time",
"docs": [
{"_id": "dfd33c43e5c559ed6f2343d6f9000aeb","_rev": "1-29ac3928e126a88b3c97b7996ed94c7b","post_code": 35801,"country": "United States","country_abbreviation": "US","places": [ { "place_name": "Huntsville", "longitude": -86.5673, "state": "Alabama", "state_abbreviation": "AL", "latitude": 34.7269 }]
},
//other results ignored
]
}

That’s right, we have no index on the post code field yet. However, the query still could be executed which is very positive. We’re used to this behaviour from other popular database engines, that queries with no matching indexes are also allowed through.

Let’s create an index via the Fauxton UI. Select the zipcodes database, expand the + button of the All Document menu item and select Mango Indexes. You’ll land on a page with a hint on how to create a new index:


Introduction to CouchDB with .NET part 18: Mango indexes and queries continued

Change the index JSON into the following:

{
"index": {
"fields": [
"post_code"
]
},
"type": "json",
"name": "post-code-index"
}

…and press the green Create Index button.

We can view the available indexes in a tabular format in Fauxton by pressing the Table button:


Introduction to CouchDB with .NET part 18: Mango indexes and queries continued

If you look at the “def” column then you’ll see that an index has an extra property value “asc”. It means ascending of course. Indexes can be ascending or descending, with ascending being more common. The index will keep the indexed field in ascending or descending order depending on how the index was specified. We’ll execute the same query as above once again in a bit and you’ll notice that the results are sorted in ascending order by the post_code field automatically.

Should you need a descending index then here’s the format:

{
"index": {
"fields": [
{"post_code": "desc" }
]
}
}

This ordering will make searches very efficient. We discussed before that view indexes must be refreshed if a new document is inserted into the database. That’s quite common for indexing in general: indexes must be updated when the underlying data changes and CouchDB is no exception. If there’s a new ZIP code document then this ZIP code must be squeezed into the ZIP code index. This implies that write operations are slower compared to the situation with no indexes at all. However, reads will be much faster. You’ll need to consider the frequency of reads and writes before you blindly start creating indexes on every single field. This latter strategy is never a good idea by the way. Normally read operations by far outweigh writes in an application which is an extra ingredient in the index planning process.

Furthermore you’ll notice that the “fields” property has an array as its value. It’s because we can insert compound indexes, i.e. indexes that consist of 2 ore more fields. If there’s a frequent search on two fields, e.g. post_code AND country then it can be a good idea to create a composite index of “post_code” and “country”. That kind of index will also help with searches on the post_code field alone. However, if there’s a search solely on the country field then the multi-key index won’t be usable as the primary sorting key is the post_code. Country is only the secondary sorting key in that case and a country-based query will need to go through all documents.

Here’s how we would enter a compound index:

{
"index": {
"fields": [
"post_code", "country"
]
},
"type": "json",
"name": "post-code-country-compound-index"
}

If we insert the above index and perform a search on the country field only then we still get the same warning as above:

{
"selector": {
"country": "United States"
}
}

"warning": "no matching index found, create an index to optimize query time",

However, if you execute the same post-code based query as in the first example above and you’ll see that the warning is gone. Our compound index covers post-code based queries as well. Feel free to keep the compound index or delete it…:


Introduction to CouchDB with .NET part 18: Mango indexes and queries continued

…and recreate the index with the post_code field only, it’s up to you. Feel free to play around with single and compound indexes, ascending and descending etc. Note that currently we cannot define different sort orders for the different members of a compound key. The following will fail:

{
"index": {
"fields": [
{"post_code": "asc"}, {"country": "desc"}
]
},
"type": "json",
"name": "post-code-country-compound-index"
}

…with the following message:

“Sorts currently only support a single direction for all fields.”

This feature may be implemented in the future though.

Querying in Fauxton

We can execute queries via Fauxton as well via the following menu item:


Introduction to CouchDB with .NET part 18: Mango indexes and queries continued

Then we need to provide the selector and press the green button:


Introduction to CouchDB with .NET part 18: Mango indexes and queries continued

You can execute the demo queries via Fauxton or the HTTP API as you wish.

Before we continue let’s prove quickly that Mango indexes are in fact view design documents. The index was added into a design document and it will be listed in Fauxton:


Introduction to CouchDB with .NET part 18: Mango indexes and queries continued
Click on the design document ID and you’ll be shown

本文数据库(综合)相关术语:系统安全软件

主题: CouchDBMango
分页:12
转载请注明
本文标题:Introduction to CouchDB with .NET part 18: Mango indexes and queries continued
本站链接:http://www.codesec.net/view/561390.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(综合) | 评论(0) | 阅读(35)