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.