Introduction to CouchDB with .NET part 17: starting with Mango queries

Introduction

In the previous post we discussed update design documents in CouchDB. Update functions make updating a document easier. They are not executed automatically when a document is updated. Instead, they must be called actively through a HTTP call. Update functions can make the update process easier since we don’t need to supply the revision ID. Also, they can have their own logic and add new rows to the document. An additional benefit is that we can send in the fields to be updated through the JSON body of the HTTP request. In other words we don’t need to provide all the properties of the document like in the case of a normal update we saw earlier.

In this post we’ll start looking into a brand new feature of CouchDB 2.0: Mango queries. A large part of the post is dedicated to setting up a ZIP code database that is slightly bigger than the demo databases we’ve been working with so far.

Mango queries

Mango is a JavaScript and JSON based query language for CouchDB documents. It was influenced by the JavaScript query language of MongoDB. If you have experience with MongoDB then you’ll recognise some elements, especially when it comes to operators.

Mango queries are a significant addition to CouchDB. Before CouchDB 2.0 developers were constrained to create views, lists, shows and MapReduce functions to query even simple things like “select all cities with a population larger than 1 million”. There was no room for ad-hoc queries. I would even argue that a number of developers were seriously put off by the lack of a true query language.

With Mango the CouchDB querying options have been largely enhanced. It still has some limitations but it’s a great step forward. Also, a Mango index is really a view-based index behind the scenes as we’ll see soon. We don’t have to create a Mango index on every single property that we want to query but it’s recommended for query optimisation to have an index on frequently queried fields.

Preparation

We’ll create a small US ZIP code database. I used the Zippopotamus online ZIP code service to extract a couple of random ZIP codes from the US. Add a new database to CouchDB called zipcodes and add the following documents. It’s best to use our knowledge of batch insertions we saw in this post.

I’ve prepared the JSON payload to the bulk modification endpoint.

POST http://localhost:5984/zipcodes/_bulk_docs

{
  "docs": [
    {
      "post_code": 35801,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Huntsville",
          "longitude": -86.5673,
          "state": "Alabama",
          "state_abbreviation": "AL",
          "latitude": 34.7269
        }
      ]
    },
    {
      "post_code": 35816,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Huntsville",
          "longitude": -86.6249,
          "state": "Alabama",
          "state_abbreviation": "AL",
          "latitude": 34.7389
        }
      ]
    },
    {
      "post_code": 99501,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Anchorage",
          "longitude": -149.8761,
          "state": "Alaska",
          "state_abbreviation": "AK",
          "latitude": 61.2116
        }
      ]
    },
    {
      "post_code": 99524,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Anchorage",
          "longitude": -149.4403,
          "state": "Alaska",
          "state_abbreviation": "AK",
          "latitude": 61.1089
        }
      ]
    },
    {
      "post_code": 85001,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Phoenix",
          "longitude": -112.3518,
          "state": "Arizona",
          "state_abbreviation": "AZ",
          "latitude": 33.704
        }
      ]
    },
    {
      "post_code": 85055,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Phoenix",
          "longitude": -112.1872,
          "state": "Arizona",
          "state_abbreviation": "AZ",
          "latitude": 33.2765
        }
      ]
    },
    {
      "post_code": 72201,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Little Rock",
          "longitude": -92.2819,
          "state": "Arkansas",
          "state_abbreviation": "AR",
          "latitude": 34.7483
        }
      ]
    },
    {
      "post_code": 72217,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Little Rock",
          "longitude": -92.1702,
          "state": "Arkansas",
          "state_abbreviation": "AR",
          "latitude": 34.8302
        }
      ]
    },
    {
      "post_code": 94203,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Sacramento",
          "longitude": -121.5554,
          "state": "California",
          "state_abbreviation": "CA",
          "latitude": 38.3805
        }
      ]
    },
    {
      "post_code": 94209,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Sacramento",
          "longitude": -121.4444,
          "state": "California",
          "state_abbreviation": "CA",
          "latitude": 38.3774
        }
      ]
    },
    {
      "post_code": 90001,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Los Angeles",
          "longitude": -118.2479,
          "state": "California",
          "state_abbreviation": "CA",
          "latitude": 33.9731
        }
      ]
    },
    {
      "post_code": 90089,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Los Angeles",
          "longitude": -118.2987,
          "state": "California",
          "state_abbreviation": "CA",
          "latitude": 33.7866
        }
      ]
    },
    {
      "post_code": 90209,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Beverly Hills",
          "longitude": -118.2987,
          "state": "California",
          "state_abbreviation": "CA",
          "latitude": 33.7866
        }
      ]
    },
    {
      "post_code": 90213,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Beverly Hills",
          "longitude": -118.2987,
          "state": "California",
          "state_abbreviation": "CA",
          "latitude": 33.7866
        }
      ]
    },
    {
      "post_code": 80201,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Denver",
          "longitude": -104.8568,
          "state": "Colorado",
          "state_abbreviation": "CO",
          "latitude": 39.7263
        }
      ]
    },
    {
      "post_code": 80239,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Denver",
          "longitude": -104.8288,
          "state": "Colorado",
          "state_abbreviation": "CO",
          "latitude": 39.7878
        }
      ]
    },
    {
      "post_code": 6101,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Hartford",
          "longitude": -72.6771,
          "state": "Connecticut",
          "state_abbreviation": "CT",
          "latitude": 41.7801
        }
      ]
    },
    {
      "post_code": 6112,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Hartford",
          "longitude": -72.6964,
          "state": "Connecticut",
          "state_abbreviation": "CT",
          "latitude": 41.7905
        }
      ]
    },
    {
      "post_code": 19901,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Dover",
          "longitude": -75.4955,
          "state": "Delaware",
          "state_abbreviation": "DE",
          "latitude": 39.1564
        }
      ]
    },
    {
      "post_code": 19905,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Dover",
          "longitude": -75.448,
          "state": "Delaware",
          "state_abbreviation": "DE",
          "latitude": 39.1087
        }
      ]
    },
    {
      "post_code": 20001,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Washington",
          "longitude": -77.0177,
          "state": "District of Columbia",
          "state_abbreviation": "DC",
          "latitude": 38.9122
        }
      ]
    },
    {
      "post_code": 20020,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Washington",
          "longitude": -76.9742,
          "state": "District of Columbia",
          "state_abbreviation": "DC",
          "latitude": 38.86
        }
      ]
    },
    {
      "post_code": 32501,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Pensacola",
          "longitude": -87.2248,
          "state": "Florida",
          "state_abbreviation": "FL",
          "latitude": 30.4223
        }
      ]
    },
    {
      "post_code": 32509,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Pensacola",
          "longitude": -87.3403,
          "state": "Florida",
          "state_abbreviation": "FL",
          "latitude": 30.4643
        }
      ]
    },
    {
      "post_code": 33124,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Miami",
          "longitude": -80.4582,
          "state": "Florida",
          "state_abbreviation": "FL",
          "latitude": 25.5584
        }
      ]
    },
    {
      "post_code": 33190,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Miami",
          "longitude": -80.3483,
          "state": "Florida",
          "state_abbreviation": "FL",
          "latitude": 25.5593
        }
      ]
    },
    {
      "post_code": 32801,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Orlando",
          "longitude": -81.3727,
          "state": "Florida",
          "state_abbreviation": "FL",
          "latitude": 28.5399
        }
      ]
    },
    {
      "post_code": 32837,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Orlando",
          "longitude": -81.4179,
          "state": "Florida",
          "state_abbreviation": "FL",
          "latitude": 28.3949
        }
      ]
    },
    {
      "post_code": 30301,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Atlanta",
          "longitude": -84.474,
          "state": "Georgia",
          "state_abbreviation": "GA",
          "latitude": 33.8444
        }
      ]
    },
    {
      "post_code": 30381,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Atlanta",
          "longitude": -84.474,
          "state": "Georgia",
          "state_abbreviation": "GA",
          "latitude": 33.8444
        }
      ]
    },
    {
      "post_code": 96801,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Honolulu",
          "longitude": -168.0218,
          "state": "Hawaii",
          "state_abbreviation": "HI",
          "latitude": 24.8598
        }
      ]
    },
    {
      "post_code": 96830,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Honolulu",
          "longitude": -157.834,
          "state": "Hawaii",
          "state_abbreviation": "HI",
          "latitude": 21.2841
        }
      ]
    },
    {
      "post_code": 83254,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Montpelier",
          "longitude": -111.3195,
          "state": "Idaho",
          "state_abbreviation": "ID",
          "latitude": 42.352
        }
      ]
    },
    {
      "post_code": 60601,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Chicago",
          "longitude": -87.6181,
          "state": "Illinois",
          "state_abbreviation": "IL",
          "latitude": 41.8858
        }
      ]
    },
    {
      "post_code": 60641,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Chicago",
          "longitude": -87.7474,
          "state": "Illinois",
          "state_abbreviation": "IL",
          "latitude": 41.9453
        }
      ]
    },
    {
      "post_code": 62701,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Springfield",
          "longitude": -89.6495,
          "state": "Illinois",
          "state_abbreviation": "IL",
          "latitude": 39.8
        }
      ]
    },
    {
      "post_code": 62709,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Springfield",
          "longitude": -89.606,
          "state": "Illinois",
          "state_abbreviation": "IL",
          "latitude": 39.7495
        }
      ]
    },
    {
      "post_code": 46201,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Indianapolis",
          "longitude": -86.1093,
          "state": "Indiana",
          "state_abbreviation": "IN",
          "latitude": 39.775
        }
      ]
    },
    {
      "post_code": 46209,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Indianapolis",
          "longitude": -86.1328,
          "state": "Indiana",
          "state_abbreviation": "IN",
          "latitude": 39.7795
        }
      ]
    },
    {
      "post_code": 52801,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Davenport",
          "longitude": -90.5745,
          "state": "Iowa",
          "state_abbreviation": "IA",
          "latitude": 41.5208
        }
      ]
    },
    {
      "post_code": 52809,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Davenport",
          "longitude": -90.6063,
          "state": "Iowa",
          "state_abbreviation": "IA",
          "latitude": 41.613
        }
      ]
    },
    {
      "post_code": 50301,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Des Moines",
          "longitude": -93.5722,
          "state": "Iowa",
          "state_abbreviation": "IA",
          "latitude": 41.6727
        }
      ]
    },
    {
      "post_code": 50323,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Urbandale",
          "longitude": -93.7717,
          "state": "Iowa",
          "state_abbreviation": "IA",
          "latitude": 41.6294
        }
      ]
    },
    {
      "post_code": 67201,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Wichita",
          "longitude": -97.259,
          "state": "Kansas",
          "state_abbreviation": "KS",
          "latitude": 37.652
        }
      ]
    },
    {
      "post_code": 67221,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Mcconnell Afb",
          "longitude": -97.2979,
          "state": "Kansas",
          "state_abbreviation": "KS",
          "latitude": 37.6066
        },
        {
          "place_name": "Mc Connell A F B",
          "longitude": -97.2663,
          "state": "Kansas",
          "state_abbreviation": "KS",
          "latitude": 37.6282
        }
      ]
    },
    {
      "post_code": 41701,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Hazard",
          "longitude": -83.1912,
          "state": "Kentucky",
          "state_abbreviation": "KY",
          "latitude": 37.2983
        }
      ]
    },
    {
      "post_code": 41702,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Hazard",
          "longitude": -83.2065,
          "state": "Kentucky",
          "state_abbreviation": "KY",
          "latitude": 37.3201
        }
      ]
    },
    {
      "post_code": 70112,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "New Orleans",
          "longitude": -90.0753,
          "state": "Louisiana",
          "state_abbreviation": "LA",
          "latitude": 29.9605
        }
      ]
    },
    {
      "post_code": 70119,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "New Orleans",
          "longitude": -90.0852,
          "state": "Louisiana",
          "state_abbreviation": "LA",
          "latitude": 29.9746
        }
      ]
    },
    {
      "post_code": 4032,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Freeport",
          "longitude": -70.1031,
          "state": "Maine",
          "state_abbreviation": "ME",
          "latitude": 43.857
        }
      ]
    },
    {
      "post_code": 4034,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Freeport",
          "longitude": -70.1031,
          "state": "Maine",
          "state_abbreviation": "ME",
          "latitude": 43.857
        }
      ]
    },
    {
      "post_code": 21201,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Baltimore",
          "longitude": -76.6252,
          "state": "Maryland",
          "state_abbreviation": "MD",
          "latitude": 39.2946
        }
      ]
    },
    {
      "post_code": 21237,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Rosedale",
          "longitude": -76.5014,
          "state": "Maryland",
          "state_abbreviation": "MD",
          "latitude": 39.3361
        }
      ]
    },
    {
      "post_code": 2101,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Boston",
          "longitude": -71.027,
          "state": "Massachusetts",
          "state_abbreviation": "MA",
          "latitude": 42.3706
        }
      ]
    },
    {
      "post_code": 2137,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Readville",
          "longitude": -70.9196,
          "state": "Massachusetts",
          "state_abbreviation": "MA",
          "latitude": 42.3389
        }
      ]
    },
    {
      "post_code": 49036,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Coldwater",
          "longitude": -85.0057,
          "state": "Michigan",
          "state_abbreviation": "MI",
          "latitude": 41.9255
        }
      ]
    },
    {
      "post_code": 49734,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Gaylord",
          "longitude": -84.6122,
          "state": "Michigan",
          "state_abbreviation": "MI",
          "latitude": 45.0284
        }
      ]
    },
    {
      "post_code": 49735,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Gaylord",
          "longitude": -84.6723,
          "state": "Michigan",
          "state_abbreviation": "MI",
          "latitude": 45.0125
        }
      ]
    },
    {
      "post_code": 55801,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Duluth",
          "longitude": -92.0019,
          "state": "Minnesota",
          "state_abbreviation": "MN",
          "latitude": 47.0056
        }
      ]
    },
    {
      "post_code": 55808,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Duluth",
          "longitude": -92.2226,
          "state": "Minnesota",
          "state_abbreviation": "MN",
          "latitude": 46.681
        }
      ]
    },
    {
      "post_code": 39530,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Biloxi",
          "longitude": -88.8971,
          "state": "Mississippi",
          "state_abbreviation": "MS",
          "latitude": 30.4035
        }
      ]
    },
    {
      "post_code": 39535,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Biloxi",
          "longitude": -89.0684,
          "state": "Mississippi",
          "state_abbreviation": "MS",
          "latitude": 30.4158
        }
      ]
    },
    {
      "post_code": 63101,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Saint Louis",
          "longitude": -90.1913,
          "state": "Missouri",
          "state_abbreviation": "MO",
          "latitude": 38.6346
        }
      ]
    },
    {
      "post_code": 63141,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Saint Louis",
          "longitude": -90.4542,
          "state": "Missouri",
          "state_abbreviation": "MO",
          "latitude": 38.6565
        }
      ]
    },
    {
      "post_code": 59044,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Laurel",
          "longitude": -108.769,
          "state": "Montana",
          "state_abbreviation": "MT",
          "latitude": 45.6745
        }
      ]
    },
    {
      "post_code": 68901,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Hastings",
          "longitude": -98.3911,
          "state": "Nebraska",
          "state_abbreviation": "NE",
          "latitude": 40.5877
        }
      ]
    },
    {
      "post_code": 68902,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Hastings",
          "longitude": -98.3972,
          "state": "Nebraska",
          "state_abbreviation": "NE",
          "latitude": 40.5896
        }
      ]
    },
    {
      "post_code": 89501,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Reno",
          "longitude": -119.8113,
          "state": "Nevada",
          "state_abbreviation": "NV",
          "latitude": 39.5268
        }
      ]
    },
    {
      "post_code": 89513,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Reno",
          "longitude": -119.2937,
          "state": "Nevada",
          "state_abbreviation": "NV",
          "latitude": 39.6319
        }
      ]
    },
    {
      "post_code": 3217,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Ashland",
          "longitude": -71.6121,
          "state": "New Hampshire",
          "state_abbreviation": "NH",
          "latitude": 43.7034
        }
      ]
    },
    {
      "post_code": 7039,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Livingston",
          "longitude": -74.3202,
          "state": "New Jersey",
          "state_abbreviation": "NJ",
          "latitude": 40.7896
        }
      ]
    },
    {
      "post_code": 87506,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Santa Fe",
          "longitude": -105.9886,
          "state": "New Mexico",
          "state_abbreviation": "NM",
          "latitude": 35.8195
        }
      ]
    },
    {
      "post_code": 10001,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "New York City",
          "longitude": -73.9967,
          "state": "New York",
          "state_abbreviation": "NY",
          "latitude": 40.7484
        }
      ]
    },
    {
      "post_code": 27565,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Oxford",
          "longitude": -78.6134,
          "state": "North Carolina",
          "state_abbreviation": "NC",
          "latitude": 36.3313
        }
      ]
    },
    {
      "post_code": 58282,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Walhalla",
          "longitude": -97.7974,
          "state": "North Dakota",
          "state_abbreviation": "ND",
          "latitude": 48.8808
        }
      ]
    },
    {
      "post_code": 44101,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Cleveland",
          "longitude": -81.5996,
          "state": "Ohio",
          "state_abbreviation": "OH",
          "latitude": 41.5234
        }
      ]
    },
    {
      "post_code": 44179,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Cleveland",
          "longitude": -81.6728,
          "state": "Ohio",
          "state_abbreviation": "OH",
          "latitude": 41.6857
        }
      ]
    },
    {
      "post_code": 74101,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Tulsa",
          "longitude": -95.8687,
          "state": "Oklahoma",
          "state_abbreviation": "OK",
          "latitude": 36.0391
        }
      ]
    },
    {
      "post_code": 97201,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Portland",
          "longitude": -122.6897,
          "state": "Oregon",
          "state_abbreviation": "OR",
          "latitude": 45.5078
        }
      ]
    },
    {
      "post_code": 15201,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Pittsburgh",
          "longitude": -79.9528,
          "state": "Pennsylvania",
          "state_abbreviation": "PA",
          "latitude": 40.4752
        }
      ]
    },
    {
      "post_code": 2840,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Newport",
          "longitude": -71.3271,
          "state": "Rhode Island",
          "state_abbreviation": "RI",
          "latitude": 41.4876
        }
      ]
    },
    {
      "post_code": 29020,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Camden",
          "longitude": -80.591,
          "state": "South Carolina",
          "state_abbreviation": "SC",
          "latitude": 34.2696
        }
      ]
    },
    {
      "post_code": 57401,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Aberdeen",
          "longitude": -98.4856,
          "state": "South Dakota",
          "state_abbreviation": "SD",
          "latitude": 45.4661
        }
      ]
    },
    {
      "post_code": 37201,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Nashville",
          "longitude": -86.7781,
          "state": "Tennessee",
          "state_abbreviation": "TN",
          "latitude": 36.1657
        }
      ]
    },
    {
      "post_code": 78701,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Austin",
          "longitude": -97.7426,
          "state": "Texas",
          "state_abbreviation": "TX",
          "latitude": 30.2713
        }
      ]
    },
    {
      "post_code": 84321,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Logan",
          "longitude": -111.8226,
          "state": "Utah",
          "state_abbreviation": "UT",
          "latitude": 41.747
        }
      ]
    },
    {
      "post_code": 5751,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Killington",
          "longitude": -72.7963,
          "state": "Vermont",
          "state_abbreviation": "VT",
          "latitude": 43.6634
        }
      ]
    },
    {
      "post_code": 24517,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Altavista",
          "longitude": -79.2911,
          "state": "Virginia",
          "state_abbreviation": "VA",
          "latitude": 37.1222
        }
      ]
    },
    {
      "post_code": 98004,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Bellevue",
          "longitude": -122.2072,
          "state": "Washington",
          "state_abbreviation": "WA",
          "latitude": 47.6155
        }
      ]
    },
    {
      "post_code": 25813,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Beaver",
          "longitude": -81.0731,
          "state": "West Virginia",
          "state_abbreviation": "WV",
          "latitude": 37.7516
        }
      ]
    },
    {
      "post_code": 53201,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Milwaukee",
          "longitude": -87.9584,
          "state": "Wisconsin",
          "state_abbreviation": "WI",
          "latitude": 43.0113
        }
      ]
    },
    {
      "post_code": 82941,
      "country": "United States",
      "country_abbreviation": "US",
      "places": [
        {
          "place_name": "Pinedale",
          "longitude": -109.8561,
          "state": "Wyoming",
          "state_abbreviation": "WY",
          "latitude": 42.8543
        }
      ]
    }
  ]
}

If everything goes fine then 92 records should be inserted into the zipcodes database.

Very first example: find a record by ID

It turns out that every database has one Mango index by default.

Execute the following GET request:

http://localhost:5984/zipcodes/_index

This retrieves all the indexes from the database. In this case we have the following:

{
  "total_rows": 1,
  "indexes": [
    {
      "ddoc": null,
      "name": "_all_docs",
      "type": "special",
      "def": {
        "fields": [
          {
            "_id": "asc"
          }
        ]
      }
    }
  ]
}

We have an ascending index called “_all_docs” on the _id field. That’s probably not too surprising as unique index fields tend to be indexed by default in databases.

The gateway into querying with Mango is the POST /_find HTTP API endpoint. It accepts a JSON object with well-defined properties:

  • selector: a JSON object that describes the condition for the selection, similar to the WHERE clause of an SQL statement
  • limit and skip: limit the number of results and skip a number of them, most often used for paging together
  • sort: sort the result by a field
  • fields: which properties to return, similar to the column names after a SELECT SQL statement
  • use_index: declare which index to use to fulfil the query. Normally Mango can select the best query path with the existing indexes but this option forces it to select a specific index.

The only mandatory field is the selector.

Here’s how we can find a document with an ID. The ZIP code 35801 has the ID 3031afee194a8e4295c021221a03f453 in my instance of the zipcodes database:

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

{
	"selector": {
		"_id": "3031afee194a8e4295c021221a03f453"
	}
}

Here’s the document returned:

{
  "docs": [
    {
      "_id": "3031afee194a8e4295c021221a03f453",
      "_rev": "1-021bec0a190e1b2a16a7423c79b3731a",
      "post code": "35801",
      "country": "United States",
      "country abbreviation": "US",
      "places": [
        {
          "place name": "Huntsville",
          "longitude": "-86.5673",
          "state": "Alabama",
          "state abbreviation": "AL",
          "latitude": "34.7269"
        }
      ]
    }
  ]
}

That’s the most simplistic Mango query out there I would say. The selector is a JSON document which declares that the _id property must be equal to a certain value. The query result is wrapped in an array called “docs”.

We’ll continue to explore Mango queries in the next post.

You can view all posts related to data storage on this blog here.

Advertisements

About Andras Nemes
I'm a .NET/Java developer living and working in Stockholm, Sweden.

One Response to Introduction to CouchDB with .NET part 17: starting with Mango queries

  1. Pingback: CouchDB Weekly News, June 22, 2017 – CouchDB Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

ultimatemindsettoday

A great WordPress.com site

iReadable { }

.NET Tips & Tricks

Robin Sedlaczek's Blog

Developer on Microsoft Technologies

HarsH ReaLiTy

A Good Blog is Hard to Find

Softwarearchitektur in der Praxis

Wissenswertes zu Webentwicklung, Domain-Driven Design und Microservices

the software architecture

thoughts, ideas, diagrams,enterprise code, design pattern , solution designs

Technology Talks

on Microsoft technologies, Web, Android and others

Software Engineering

Web development

Disparate Opinions

Various tidbits

chsakell's Blog

Anything around ASP.NET MVC,WEB API, WCF, Entity Framework & AngularJS

Cyber Matters

Bite-size insight on Cyber Security for the not too technical.

Guru N Guns's

OneSolution To dOTnET.

Johnny Zraiby

Measuring programming progress by lines of code is like measuring aircraft building progress by weight.

%d bloggers like this: