Introduction to CouchDB with .NET part 19: Mango query operators

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.

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 19: Mango query operators

  1. Pingback: CouchDB Weekly News, June 29, 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: