1

I have the following db structure:

{"_id": "0096874","genre": ["Adventure","Comedy", "Sci-Fi" ]}
{"_id": "0099088","genre": ["Comedy", "Sci-Fi", "Western"]}

and like to query it like I could do in mongodb

db.movies.find({genre: {$in: ["Comedy"]}})

It works when i use a text index for the whole database, but that seems very wasteful:

// index
    {
      "index": {},
      "type": "text"
    }
//query
{
  "selector": {
    "genre": {
      "$in": ["Comedy"]
    }
  },
  "fields": [
    "_id",
    "genre"
  ]
}

The following index does not work:

{
  "index": {
    "fields": [
      "genre"
    ]
  },
  "type": "json"
}

What is the correct index for cloudant query, which does not index the whole db? Thanks for your help

Aurélien Bénel
  • 3,775
  • 24
  • 45
Andrej
  • 326
  • 3
  • 7

1 Answers1

2

You had it almost correct. Your index is right, but you need to throw in a selector to get all IDs https://cloudant.com/blog/mango-json-vs-text-indexes/.

This isn't a great solution performance-wise, as Tony says,

The reason this works is, again, because Mango performs the above $in operation as a filtering mechanism against all the documents. As we saw in the conclusion of the previous section on JSON syntax, the performance tradeoff with the query above is that it, essentially, performs a full index scan and then applies a filter.

{
  "selector": {
    "_id": {
      "$gt": null
    }, 
    "genre": {
      "$in": ["Western"]
    }
  },
  "fields": [
    "_id",
    "genre"
  ],
  "sort": [
    {
      "_id": "asc"
    }
  ]
}
Raj
  • 544
  • 2
  • 10
  • Its a shame that mango needs to do a filter operation. In mongodb one can put a real index on an array. Couldn't one also do a simple map in couchdb where all array members are emitted individually? (doc) -> if doc.genre then doc.genre.forEach (item) -> emit item. just my 2ct. – Andrej Dec 07 '15 at 20:35
  • So how could I use the alternative and more performant on an array-like value? I don't see a way to use cloudants new indexing methods for this I'm not sure where to put something like this https://gist.github.com/tannerlinsley/34d6f3511efd2a74ec49 – tannerlinsley Jan 11 '16 at 00:50