Introduction to CouchDB with .NET part 19: Mango query operators
June 26, 2017 2 Comments
Introduction
In the previous post we continued our discussion about Mango queries in CouchDB. A large part of the post concentrated on indexing, what indexes are, the different types of indexes, how they are created and how they are invoked in a Mango query by the query planner. We saw how a warning was issued when we executed a query on a field that was not indexed. We don’t have to create an index on every property we want to query but the most frequently queried fields should really be indexed to speed up read operations. Mango indexes are translated into view design documents. Indexes come at a price as they need to be updated when the database is updated. Finally we looked at field selection, skipping, sorting and limiting in JSON queries.
In this post we’ll look at examples of Mango operators.
Mango operators
We’ve already seen the $lt operator in action:
"selector": {
"post_code": {"$lt": 40000}
}
Operators and values are paired up in JSON documents like above. Operators always begin with a $ sign which will seem familiar to MongoDb users. Many Mango operators are identical to MongoDb operators.
$gt corresponds to greater-than, $gte to greater-than-or-equal-to and $lte means less-than-or-equal-to.
The AND and EQUAL operators
We’ve also seen the equal operator in action although without the operator itself:
{
"selector": {
"post_code": 35801
}
}
Equality doesn’t need an operator. We can rewrite the above with the $eq operator if we want to:
{
"selector": {
"post_code": {"$eq": 35801}
}
}
The two solutions are identical.
The $eq operator has a negation: $ne which stands for not-equal-to.
Similarly we can have multiple equality conditions, we just need to add them to the selector object:
{
"selector": {
"post_code": 35801,
"country": "United States"
}
}
That’s an implicit AND operation. We can use $and if we want to be explicit, though we need to type a bit more:
{
"selector": {
"$and": [
{
"post_code": {
"$eq": 35801
}
},
{
"country": {
"$eq": "United States"
}
}
]
}
}
The $and operator is an array where we list the conditions for the search. This has the same function as the WHERE clause in SQL statements.
The OR, NOR and NOT operators
The $or operator is an array and has the same function as the IN statement in a WHERE clause in SQL:
{
"selector": {
"$or": [{"post_code": 35801}, {"post_code": 80239}]
}
}
The above selector will find two places in our small ZIP codes database: Huntsville and Denver.
The $nor operator is the inverse of $or, it is equivalent to NOT IN in SQL:
{
"selector": {
"$nor": [{"post_code": 35801}, {"post_code": 80239}]
}
}
This finds all places OTHER THAN the two located by $or.
$not is also quite intuitive. It negates a selector like the NOT keyword in a WHERE clause:
{
"selector": {
"$not": {"country": "United States"}
}
}
That returns no documents since all ZIP codes are from the US.
Querying subdocuments
The ZIP codes DB is not an ideal for showing how to query subdocuments, i.e. JSON documents within a JSON document. However, imagine that instead of an array of objects…:
"places": [
{
"place_name": "Denver",
"longitude": -104.8288,
"state": "Colorado",
"state_abbreviation": "CO",
"latitude": 39.7878
}
]
…we have a single object for places like here:
"place":
{
"place_name": "Denver",
"longitude": -104.8288,
"state": "Colorado",
"state_abbreviation": "CO",
"latitude": 39.7878
}
Then if we want to search for a ZIP code whose place_name is Denver then we’d write the following selector:
{
"place": {
"place_name": { "$eq": "Denver" }
}
}
…i.e. we dig down the JSON hierarchy in the selector’s JSON object.
If you want to know now how to find the ZIP codes of Denver in the database then here’s the selector for it:
{
"selector": {
"places": {
"$elemMatch": {"place_name": "Denver"}
}
}
}
…which returns two records, one with post_code 80201 and another with 80239.
$elemMatch is an array operator. It requires a selector in which we describe what element the array must include. The above example states that we want all ZIP codes whose “places” array has an element where “place_name” is equal to Denver.
Mango has a number of other array related operators which we’ll discuss in the next post.
Object related operators
The $exists operator selects those documents which have a specific field:
{
"selector": {
"country": {"$exists": true}
}
}
This returns all documents since all of them have a field called “country”. By setting the boolean value to false we’ll obviously select the documents that do NOT have the specified field.
The $type operator filters documents based on the type of a specific field. The valid types in the selector are “boolean”, “null”, “string, “number”, “array”, and “object”. Here’s an example:
{
"selector": {
"country": {"$type": "string"}
}
}
All countries are strings so we get back all the documents.
Preparation for next post
We’ll create two new databases to make our examples more exciting for the array related operators.
First create a new database called people. Then call the batch insert API endpoint to add 10 documents:
POST http://localhost:5984/people/_bulk_docs
Here’s the payload:
{
"docs": [{
"name": "John",
"age": 34,
"sports": ["football", "badminton", "squash"],
"grades": [50, 60, 45, 43]
},
{
"name": "Mary",
"age": 28,
"sports": ["cricket", "badminton"],
"grades": [90, 60, 53]
},
{
"name": "William",
"age": 29,
"sports": ["tennis", "basketball", "squash"],
"grades": [30, 40, 64, 90, 78]
},
{
"name": "Bridget",
"age": 45,
"sports": ["baseball", "athletics", "running"],
"grades": [50, 90, 95, 40]
},
{
"name": "Susan",
"age": 40,
"sports": ["football", "boxing", "karate"],
"grades": [70, 80, 78]
},
{
"name": "John",
"age": 39,
"sports": ["cyckling", "swimming", "basketball"],
"grades": [10, 90, 60, 50]
},
{
"name": "John",
"age": 31,
"sports": ["handball", "skating", "baseball"],
"grades": [55, 65]
},
{
"name": "Joanna",
"age": 28,
"sports": ["judo", "karate", "running"],
"grades": [88, 77, 66, 55]
},
{
"name": "Christina",
"age": 27,
"sports": ["running", "swimming", "badminton"],
"grades": [51, 61, 46, 44]
},
{
"name": "Homer",
"age": 48,
"sports": ["football", "skating", "boxing"],
"grades": [45, 47, 90, 99]
}
]
}
Next create another database called restaurants. Add the following data via batch insert:
POST http://localhost:5984/restaurants/_bulk_docs
{
"docs": [
{
"address": {
"building": "2780",
"coord": [-73.98241999999999, 40.579505],
"street": "Stillwell Avenue",
"zipcode": "11224"
},
"borough": "Brooklyn",
"cuisine": "American ",
"grades": [{
"grade": "A",
"score": 5
}, {
"grade": "A",
"score": 7
}, {
"grade": "A",
"score": 12
}, {
"grade": "A",
"score": 12
}],
"name": "Riviera Caterer"
},
{
"address": {
"building": "1007",
"coord": [-73.856077, 40.848447],
"street": "Morris Park Ave",
"zipcode": "10462"
},
"borough": "Bronx",
"cuisine": "Bakery",
"grades": [{
"grade": "A",
"score": 2
}, {
"grade": "A",
"score": 6
}, {
"grade": "A",
"score": 10
}, {
"grade": "A",
"score": 9
}, {
"grade": "B",
"score": 14
}],
"name": "Morris Park Bake Shop"
},
{
"address": {
"building": "469",
"coord": [-73.961704, 40.662942],
"street": "Flatbush Avenue",
"zipcode": "11225"
},
"borough": "Brooklyn",
"cuisine": "Hamburgers",
"grades": [{
"grade": "A",
"score": 8
}, {
"grade": "B",
"score": 23
}, {
"grade": "A",
"score": 12
}, {
"grade": "A",
"score": 12
}],
"name": "Wendy'S"
},
{
"address": {
"building": "351",
"coord": [-73.98513559999999, 40.7676919],
"street": "West 57 Street",
"zipcode": "10019"
},
"borough": "Manhattan",
"cuisine": "Irish",
"grades": [{
"grade": "A",
"score": 2
}, {
"grade": "A",
"score": 11
}, {
"grade": "A",
"score": 12
}, {
"grade": "A",
"score": 12
}],
"name": "Dj Reynolds Pub And Restaurant"
},
{
"address": {
"building": "2206",
"coord": [-74.1377286, 40.6119572],
"street": "Victory Boulevard",
"zipcode": "10314"
},
"borough": "Staten Island",
"cuisine": "Jewish/Kosher",
"grades": [{
"grade": "A",
"score": 9
}, {
"grade": "A",
"score": 12
}, {
"grade": "A",
"score": 12
}, {
"grade": "A",
"score": 9
}],
"name": "Kosher Island"
},
{
"address": {
"building": "97-22",
"coord": [-73.8601152, 40.7311739],
"street": "63 Road",
"zipcode": "11374"
},
"borough": "Queens",
"cuisine": "Jewish/Kosher",
"grades": [{
"grade": "Z",
"score": 20
}, {
"grade": "A",
"score": 13
}, {
"grade": "A",
"score": 13
}, {
"grade": "B",
"score": 25
}],
"name": "Tov Kosher Kitchen"
},
{
"address": {
"building": "8825",
"coord": [-73.8803827, 40.7643124],
"street": "Astoria Boulevard",
"zipcode": "11369"
},
"borough": "Queens",
"cuisine": "American ",
"grades": [{
"grade": "Z",
"score": 38
}, {
"grade": "A",
"score": 10
}, {
"grade": "A",
"score": 7
}, {
"grade": "A",
"score": 13
}],
"name": "Brunos On The Boulevard"
},
{
"address": {
"building": "1839",
"coord": [-73.9482609, 40.6408271],
"street": "Nostrand Avenue",
"zipcode": "11226"
},
"borough": "Brooklyn",
"cuisine": "Ice Cream, Gelato, Yogurt, Ices",
"grades": [{
"grade": "A",
"score": 12
}, {
"grade": "A",
"score": 8
}, {
"grade": "A",
"score": 5
}, {
"grade": "A",
"score": 8
}],
"name": "Taste The Tropics Ice Cream"
},
{
"address": {
"building": "6409",
"coord": [-74.00528899999999, 40.628886],
"street": "11 Avenue",
"zipcode": "11219"
},
"borough": "Brooklyn",
"cuisine": "American ",
"grades": [{
"grade": "A",
"score": 12
}, {
"grade": "A",
"score": 12
}, {
"grade": "A",
"score": 11
}, {
"grade": "A",
"score": 2
}, {
"grade": "A",
"score": 11
}],
"name": "Regina Caterers"
},
{
"address": {
"building": "7114",
"coord": [-73.9068506, 40.6199034],
"street": "Avenue U",
"zipcode": "11234"
},
"borough": "Brooklyn",
"cuisine": "Delicatessen",
"grades": [{
"grade": "A",
"score": 10
}, {
"grade": "A",
"score": 10
}, {
"grade": "A",
"score": 8
}, {
"grade": "A",
"score": 10
}, {
"grade": "A",
"score": 13
}, {
"grade": "A",
"score": 9
}],
"name": "Wilken'S Fine Food"
},
{
"address": {
"building": "2300",
"coord": [-73.8786113, 40.8502883],
"street": "Southern Boulevard",
"zipcode": "10460"
},
"borough": "Bronx",
"cuisine": "American ",
"grades": [{
"grade": "A",
"score": 11
}, {
"grade": "A",
"score": 4
}, {
"grade": "A",
"score": 3
}],
"name": "Wild Asia"
},
{
"address": {
"building": "7715",
"coord": [-73.9973325, 40.61174889999999],
"street": "18 Avenue",
"zipcode": "11214"
},
"borough": "Brooklyn",
"cuisine": "American ",
"grades": [{
"grade": "A",
"score": 5
}, {
"grade": "A",
"score": 2
}, {
"grade": "A",
"score": 5
}, {
"grade": "A",
"score": 2
}],
"name": "C & C Catering Service",
"restaurant_id": "40357437"
},
{
"address": {
"building": "1",
"coord": [-73.96926909999999, 40.7685235],
"street": "East 66 Street",
"zipcode": "10065"
},
"borough": "Manhattan",
"cuisine": "American ",
"grades": [{
"grade": "A",
"score": 3
}, {
"grade": "A",
"score": 4
}, {
"grade": "A",
"score": 6
}, {
"grade": "A",
"score": 0
}],
"name": "1 East 66Th Street Kitchen"
},
{
"address": {
"building": "1269",
"coord": [-73.871194, 40.6730975],
"street": "Sutter Avenue",
"zipcode": "11208"
},
"borough": "Brooklyn",
"cuisine": "Chinese",
"grades": [{
"grade": "B",
"score": 21
}, {
"grade": "A",
"score": 7
}, {
"grade": "C",
"score": 56
}, {
"grade": "B",
"score": 27
}, {
"grade": "B",
"score": 27
}],
"name": "May May Kitchen"
},
{
"address": {
"building": "203",
"coord": [-73.97822040000001, 40.6435254],
"street": "Church Avenue",
"zipcode": "11218"
},
"borough": "Brooklyn",
"cuisine": "Ice Cream, Gelato, Yogurt, Ices",
"grades": [{
"grade": "A",
"score": 2
}, {
"grade": "A",
"score": 13
}, {
"grade": "A",
"score": 3
}, {
"grade": "P",
"score": 12
}, {
"grade": "A",
"score": 13
}],
"name": "Carvel Ice Cream"
},
{
"address": {
"building": "6909",
"coord": [-74.0259567, 40.6353674],
"street": "3 Avenue",
"zipcode": "11209"
},
"borough": "Brooklyn",
"cuisine": "Delicatessen",
"grades": [{
"grade": "A",
"score": 4
}, {
"grade": "A",
"score": 3
}, {
"grade": "A",
"score": 10
}],
"name": "Nordic Delicacies"
},
{
"address": {
"building": "522",
"coord": [-73.95171, 40.767461],
"street": "East 74 Street",
"zipcode": "10021"
},
"borough": "Manhattan",
"cuisine": "American ",
"grades": [{
"grade": "A",
"score": 12
}, {
"grade": "B",
"score": 16
}, {
"grade": "A",
"score": 9
}, {
"grade": "A",
"score": 13
}, {
"grade": "A",
"score": 11
}],
"name": "Glorious Food"
},
{
"address": {
"building": "265-15",
"coord": [-73.7032601, 40.7386417],
"street": "Hillside Avenue",
"zipcode": "11004"
},
"borough": "Queens",
"cuisine": "Ice Cream, Gelato, Yogurt, Ices",
"grades": [{
"grade": "A",
"score": 9
}, {
"grade": "A",
"score": 10
}, {
"grade": "A",
"score": 13
}],
"name": "Carvel Ice Cream"
},
{
"address": {
"building": "284",
"coord": [-73.9829239, 40.6580753],
"street": "Prospect Park West",
"zipcode": "11215"
},
"borough": "Brooklyn",
"cuisine": "American ",
"grades": [{
"grade": "A",
"score": 11
}, {
"grade": "A",
"score": 2
}, {
"grade": "A",
"score": 13
}, {
"grade": "A",
"score": 11
}],
"name": "The Movable Feast"
},
{
"address": {
"building": "129-08",
"coord": [-73.839297, 40.78147],
"street": "20 Avenue",
"zipcode": "11356"
},
"borough": "Queens",
"cuisine": "Delicatessen",
"grades": [{
"grade": "A",
"score": 12
}, {
"grade": "A",
"score": 9
}, {
"grade": "A",
"score": 7
}, {
"grade": "A",
"score": 10
}],
"name": "Sal'S Deli"
}
]
}
I got the restaurants DB schema and the above documents from a more complete MongoDB test database called restaurants. The above is only a small set of the entire data. I was unfortunately not able to find any good available test databases for CouchDB so I had to improvise. I also simplified the structure a bit and got rid of some MongoDB specific properties such as the Object ID. BTW I don’t know the difference between “grade” and “score” in the grades arrays but we’ll survive without this knowledge.
We’ll continue with the array operators and other examples in the next post.
You can view all posts related to data storage on this blog here.
About Andras Nemes
Exercises in .NET with Andras Nemes 
Pingback: CouchDB Weekly News, June 29, 2017 – CouchDB Blog
Hi Andras.
Thank you for your good examples and explanations.
I am trying to follow this series on couchdb Mango queries
Mango queries are becomming more interesting to me recently.
However, in javascript I seem to have a blank area I just can’t overcome.
For instance, if I wantd to have a query with certain fields selected as follows I just can’t get my javascript right:
{
“selector”: {
“post_code”: {“$eq”: 35801}
},
“fields”:[“places.placename”]
}
My javascript is the problem.
I just cannot get a handle on this.
JSON data can come in ANY shape or form but if one cannot write the javascript it is ‘unreachable’.
(I have tried a lot of online cources but the problem is one gets tied up in theory and it takes up too much valuable time, with little real-world results.)
Could you possibly point me to a place where I could get a real understanding of javascript.
Many thanks.