Search code examples
jsondatabasemapreducecouchdbcloudant

How to index multidimensional arrays in couchdb


I have a multidimensional array that I want to index with CouchDB (really using Cloudant). I have users which have a list of the teams that they belong to. I want to search to find every member of that team. So, get me all the User objects that have a team object with id 79d25d41d991890350af672e0b76faed. I tried to make a json index on "Teams.id", but it didn't work because it isn't a straight array but a multidimensional array.

User

{
 "_id": "683be6c086381d3edc8905dc9e948da8",
 "_rev": "238-963e54ab838935f82f54e834f501dd99",
 "type": "Feature",
 "Kind": "Profile",
 "Email": "[email protected]",
 "FirstName": "George",
 "LastName": "Castanza",
 "Teams": [
  {
   "id": "79d25d41d991890350af672e0b76faed",
   "name": "First Team",
   "level": "123"
  },
  {
   "id": "e500c1bf691b9cfc99f05634da80b6d1",
   "name": "Second Team Name",
   "level": ""
  },
  {
   "id": "4645e8a4958421f7d843d9b34c4cd9fe",
   "name": "Third Team Name",
   "level": "123"
  }
 ],
 "LastTeam": "79d25d41d991890350af672e0b76faed"
}

Solution

  • This is a lot like my response at Cloudant Selector Query but here's the deal, applied to your question:

    The easiest way to run this query is using "Cloudant Query" (or "Mango", as it's called in the forthcoming CouchDB 2.0 release) -- and not the traditional MapReduce view indexing system in CouchDB. (This blog covers the differences: https://cloudant.com/blog/mango-json-vs-text-indexes/ and this one is an overview: https://developer.ibm.com/clouddataservices/2015/11/24/cloudant-query-json-index-arrays/).

    Here's what your CQ index should look like:

    {
      "index": {
        "fields": [
          {"name": "Teams.[].id", "type": "string"}
        ]
      },
      "type": "text"
    }
    

    And what the subsequent query looks like:

    {
      "selector": {
        "Teams": {"$elemMatch": {"id": "79d25d41d991890350af672e0b76faed"}}
      },
      "fields": [
        "_id",
        "FirstName",
        "LastName"
      ]
    }
    

    You can try it yourself in the "Query" section of the Cloudant dashboard or via curl with something like this:

    curl -H "Content-Type: application/json" -X POST -d '{"selector":{"Teams":{"$elemMatch":{"id":"79d25d41d991890350af672e0b76faed"}}},"fields":["_id","FirstName","LastName"]}' https://broberg.cloudant.com/teams_test/_find
    

    That database is world-readable, so you can see the sample documents I created in there here: https://broberg.cloudant.com/teams_test/_all_docs?include_docs=true

    Dig the Seinfeld theme :D