Introduction to CouchDB with .NET part 20: Mango query array operators

Introduction

In the previous post we started looking into query operators in Mango. Query operators are prefixed with the dollar sign $ and define search operators such as greater-than, less-than-or-equal-to or not. They are quite self-explanatory and easy to use in JSON queries. At the end of the post we also set up two new databases: people and restaurants. They are better suited for the topic of this post which is operators related to arrays.

Reminder: the path to querying with Mango is POST db-name/_find, I won’t repeat it for every single example in this post

POST http://localhost:5984/people/_find
POST http://localhost:5984/restaurants/_find

…where the payload is provided in the examples.

The demo databases

Here’s a reminder of the document structure in the people database:

{
  "_id": "dfd33c43e5c559ed6f2343d6f90357f5",
  "_rev": "1-18feebc5e4de7c334096172c2710aada",
  "name": "John",
  "age": 34,
  "sports": [
    "football",
    "badminton",
    "squash"
  ],
  "grades": [
    50,
    60,
    45,
    43
  ]
}

…and here’s one restaurant document:

{
  "_id": "dfd33c43e5c559ed6f2343d6f903c164",
  "_rev": "1-15df18a3f71b20cb837094f25784fa6f",
  "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"
}

The $all operator

Say we want to find all documents where the sports array includes football and boxing. The $all operator requires an array and will return all documents whose array field includes all the provided elements:

{
	"selector": {
	    "sports": {"$all": ["football", "boxing"]}
	}
}

It returns two documents: Susan and Homer.

A restaurant document also has a grades array but it has an array of JSON documents itself. How can we select all restaurants whose grades array includes a grade A and a grade B? That’s actually for the $elemMatch operator discussed in the next section.

The $elemMatch operator

The $elemMatch operator help us apply multiple search conditions on an array field. It accepts a JSON document with a number of search criteria and will match all documents whose referenced array field includes at least one element that fulfils all of those criteria. Here’s an example:

{
	"selector": {
	    "grades": {"$elemMatch": { "$gte": 70, "$lt": 85}}
	}
}

Here we want to return all documents whose “grades” array includes at least one element which is BOTH greater than or equal to 70 AND less than 85. We have 3 documents that match: William, Susan and Joanna with the following grades arrays:

"grades": [
                30,
                40,
                64,
                90,
                78
            ]

"grades": [
                70,
                80,
                78
            ]

"grades": [
                88,
                77,
                66,
                55
            ]

We can now return to the restaurant query mentioned in the previous section: get all restaurants whose grades array includes both an A and a B grade. Let’s start with selecting all restaurants with at least one grade A:

{
	"selector": {
	    "grades": {"$elemMatch": {"grade": "A"}}
	}
}

We can pass in a sub-document to the elemMatch operator. It will look for all documents whose “grades” array includes an element “grade” whose value is “A”.

All we now need is a second condition where we want the grade to be equal to B:

{
  "selector": {
    "grades": {
      "$elemMatch": {
        "grade": "A"
      }
    },
	"grades": {
      "$elemMatch": {
        "grade": "B"
      }
    }
  }
}

Now we have all restaurants with at least one A and one B grade, e.g. “Morris Park Bake Shop” with the following grades array:

"grades": [
                {
                    "grade": "A",
                    "score": 2
                },
                {
                    "grade": "A",
                    "score": 6
                },
                {
                    "grade": "A",
                    "score": 10
                },
                {
                    "grade": "A",
                    "score": 9
                },
                {
                    "grade": "B",
                    "score": 14
                }
            ]

While we’re at it let’s see another example of elemMatch. We want to extract all restaurants whose grades array includes a grade A AND a score of more than 12 points. We can mix elemMatch with the $and operator as follows:

{
  "selector": {
    "grades": {
      "$elemMatch": {
        "$and": [
          {
            "grade": "A"
          },
          {
            "score": {"$gte": 12}           
          }
        ]
      }
    }
  }
}

The $in and $nin operators

The $in operator works like the IN clause in a WHERE statement in SQL. It accepts an array and matches all documents whose referenced array field has any of the elements in the $in array. Say we want to get all the people that exercise EITHER football OR squash:

{
	"selector": {
	    "sports": {"$in": ["football", "squash"]}
	}
}

It returns John who does both football and squash, but the result set also contains William who only has squash of the two sports in the filter.

$nin is the opposite of $in. The following will select all the people who do NOT exercise either football or squash:

{
	"selector": {
	    "sports": {"$nin": ["football", "squash"]}
	}
}

This returns among others Mary who does cricket and badminton but not football or squash.

The $size operator

The $size operator accepts an integer. It will match each document whose referenced array field includes the given number of elements. Say we want to get all the people who do three different types of sports:

{
	"selector": {
	    "sports": {"$size": 3}
	}
}

We only have Mary that matches this condition.

More examples on searching in sub-documents

The restaurant documents have a an address field which in turn is a JSON document, i.e. we have a nested JSON structure here. How can we find all restaurants with the zip code 11224? We can dig deeper in the JSON structure by digging deeper in the JSON query:

{
	"selector": {
	    "address": 
	    	{"zipcode": "11224"}
	}
}

It returns a single restaurant “Riviera Caterer”.

An alternative is the dot-notation like in the following example:

{
	"selector": {
	    "address.zipcode": "11224"
	}
}

The array operators we saw above can of course be applied to arrays in nested documents. Currently we only have one such case, which is the coordinates array of the restaurant address. It is not ideal but will do for demo purposes. We want to extract all restaurants with the coordinates -73.8601152 and 40.7311739:

{
	"selector": {
	    "address.coord": {"$in": [-73.8601152, 40.7311739]}
	}
}

This will find a single restaurant called “Tov Kosher Kitchen”.

Read the next post here.

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