Nested fields.

[hamiltonsalazar]

Hi, i have a problem with the index in nested fields.
I have an array field courses where it has nested objects. I have created this index:

{
    "index": {
        "partial_filter_selector": {
            "courses.title": {
                "$in": [
                    "javascript-profesional",
                    "php-profesional",
                    "mongoose",
                    "docker-swarm",
                    "ionic-v4"
                ]
            }
        },
        "fields": [
            "name",
            "age"
        ]
    },
    "ddoc": "courses.title:name-age",
    "name": "courses.title:name-age",
    "type": "json"
}

I want to query the courses with the title "javascript-profesional" using this index:

{
    "selector": {
        "courses.title": "javascript-profesional"
    },
    "fields": [
        "_id",
        "name",
        "age",
        "courses"
    ],
    "use_index": "courses.title:name-age"
}

But CouchDB responds with this:

{"docs":[
],
"bookmark": "nil",
"warning": "_design/courses.title:name-age was not used because it does not contain a valid index for this query.\nNo matching index found, create an index to optimize query time.\nThe number of documents examined is high in proportion to the number of results returned. Consider adding a more specific index to improve this."}

With other query:

{
    "selector": {
        "age": {
            "$lte": 40
        },
        "courses": {
                "title": "nosql-moongodb"            
        }
    },
    "fields": [
        "_id",
        "name",
        "age",
        "sex",
        "courses"
    ]
}

CouchDB responds with:

{"docs":[
],
"bookmark": "nil",
"warning": "The number of documents examined is high in proportion to the number of results returned. Consider adding a more specific index to improve this."}

But if i query with a small modification in the selector "selector": { "age": { "$lte": 40 }, "courses": { "$allMatch": { "title": "nosql-moongodb" } } }} CouchDB responds with:

{"docs":[

{"_id":"374","name":"Thiago","age":19,"sex":"M","courses":[{"title":"nosql-moongodb","progress":65,"completed":false,"tutor":{"name":"Scratch","link":"https://scratch.mit.edu/"}}]},
{"_id":"471","name":"Daniel","age":30,"sex":"M","courses":[{"title":"nosql-moongodb","progress":87,"completed":false,"tutor":{"name":"Codeacademy","link":"codecademy.com"}}]}
],
"bookmark": "g2wAAAACaAJkAA5zdGFydGtleV9kb2NpZG0AAAADNDcxaAJkAAhzdGFydGtleWsAAR5q",
"warning": "The number of documents examined is high in proportion to the number of results returned. Consider adding a more specific index to improve this."}

[rjharmon]

Your index only has name and age available. The filter on courses looks like it might be throwing away all the records, and indexing nothing - if so, then querying that index for all records give you zero results.

I haven't found a way to query array-content effectively with a Mango index. I have used a map-reduce index to do cases like your search-by-single-course scenario. Or, as I understand, a full-text-index may be useful for that.

[hamiltonsalazar]

I can solve this with the below index:

{
    "index": {
        "partial_filter_selector": {
            "courses": {
                "$elemMatch": {
                    "title": {
                        "$in": [
                            "javascript-profesional",
                            "php-profesional",
                            "mongoose",
                            "docker-swarm",
                            "ionic-v4"
                        ]
                    }
                }
            }
        },
        "fields": [
            "name",
            "age"
        ]
    },
    "ddoc": "courses.title:age",
    "name": "courses.title:age",
    "type": "json"
}

And i used the below query:

{
    "selector": {
        "courses": {
            "$elemMatch": {
                "title": {
                    "$eq": "javascript-profesional"
                }
            }
        },
        "age": {
            "$lte": 25
        }
    },
    "fields": [
        "_id",
        "name",
        "age",
        "sex",
        "courses"
    ],
    "use_index": "courses.title:age",
    "sort": ["name", "age"]
}

The response is:

{"docs":[
{"_id":"522","name":"Alice","age":24,"sex":"F","courses":[{"title":"javascript-profesional","progress":4,"completed":false,"tutor":{"name":"C├│dgioFacilito","link":"https://codigofacilito.com/"}},{"title":"flutter-sqlite","progress":1,"completed":false,"tutor":{"name":"Scratch","link":"https://scratch.mit.edu/"}},{"title":"typescript","progress":1,"completed":false,"tutor":{"name":"Codeacademy","link":"codecademy.com"}}]},
{"_id":"508","name":"Daniela","age":21,"sex":"F","courses":[{"title":"swift-introduccion","progress":56,"completed":false,"tutor":{"name":"C├│dgioFacilito","link":"https://codigofacilito.com/"}},{"title":"flutter-sqlite","progress":53,"completed":false,"tutor":{"name":"Codeacademy","link":"codecademy.com"}},{"title":"javascript-profesional","progress":52,"completed":false,"tutor":{"name":"C├│dgioFacilito","link":"https://codigofacilito.com/"}},{"title":"android-kotlin","progress":77,"completed":false,"tutor":{"name":"C├│dgioFacilito","link":"https://codigofacilito.com/"}},{"title":"flutter-sqlite","progress":93,"completed":false,"tutor":{"name":"Code.org","link":"https://code.org/"}}]},
{"_id":"620","name":"Eleanor","age":22,"sex":"F","courses":[{"title":"google-maps-flutter","progress":38,"completed":false,"tutor":{"name":"Codemonkey ","link":"https://www.codemonkey.com/"}},{"title":"swift-introduccion","progress":69,"completed":false,"tutor":{"name":"Code.org","link":"https://code.org/"}},{"title":"git-profesional","progress":65,"completed":false,"tutor":{"name":"Codeacademy","link":"codecademy.com"}},{"title":"javascript-profesional","progress":47,"completed":false,"tutor":{"name":"Codemonkey ","link":"https://www.codemonkey.com/"}},{"title":"base-datos-profesional","progress":75,"completed":false,"tutor":{"name":"C├│dgioFacilito","link":"https://codigofacilito.com/"}}]},
{"_id":"816","name":"Isla","age":21,"sex":"F","courses":[{"title":"base-datos-profesional","progress":72,"completed":false,"tutor":{"name":"Codemonkey ","link":"https://www.codemonkey.com/"}},{"title":"android-profesional","progress":79,"completed":false,"tutor":{"name":"Codeacademy","link":"codecademy.com"}},{"title":"java-profesional","progress":19,"completed":false,"tutor":{"name":"Codemonkey ","link":"https://www.codemonkey.com/"}},{"title":"javascript-profesional","progress":3,"completed":false,"tutor":{"name":"Code.org","link":"https://code.org/"}}]},
{"_id":"918","name":"Julia","age":22,"sex":"F","courses":[{"title":"java-profesional","progress":89,"completed":false,"tutor":{"name":"Scratch","link":"https://scratch.mit.edu/"}},{"title":"ionic-v5","progress":94,"completed":false,"tutor":{"name":"Scratch","link":"https://scratch.mit.edu/"}},{"title":"nosql-moongodb","progress":49,"completed":false,"tutor":{"name":"Codemonkey ","link":"https://www.codemonkey.com/"}},{"title":"javascript-profesional","progress":72,"completed":false,"tutor":{"name":"Codemonkey ","link":"https://www.codemonkey.com/"}}]},
{"_id":"851","name":"Mario","age":24,"sex":"M","courses":[{"title":"introduccion-nginx","progress":58,"completed":false,"tutor":{"name":"C├│dgioFacilito","link":"https://codigofacilito.com/"}},{"title":"android-kotlin","progress":67,"completed":false,"tutor":{"name":"Code.org","link":"https://code.org/"}},{"title":"javascript-profesional","progress":27,"completed":false,"tutor":{"name":"Scratch","link":"https://scratch.mit.edu/"}},{"title":"mongoose","progress":7,"completed":false,"tutor":{"name":"Scratch","link":"https://scratch.mit.edu/"}}]},
{"_id":"804","name":"Regina","age":20,"sex":"F","courses":[{"title":"nosql-moongodb","progress":65,"completed":false,"tutor":{"name":"Codemonkey ","link":"https://www.codemonkey.com/"}},{"title":"javascript-profesional","progress":43,"completed":false,"tutor":{"name":"Scratch","link":"https://scratch.mit.edu/"}},{"title":"mongoose","progress":77,"completed":false,"tutor":{"name":"Code.org","link":"https://code.org/"}},{"title":"introduccion-nginx","progress":22,"completed":false,"tutor":{"name":"Code.org","link":"https://code.org/"}}]},
{"_id":"183","name":"Renata","age":25,"sex":"F","courses":[{"title":"frontend-profesional","progress":4,"completed":false,"tutor":{"name":"C├│dgioFacilito","link":"https://codigofacilito.com/"}},{"title":"javascript-profesional","progress":51,"completed":false,"tutor":{"name":"Codemonkey ","link":"https://www.codemonkey.com/"}},{"title":"android-profesional","progress":57,"completed":false,"tutor":{"name":"Codemonkey ","link":"https://www.codemonkey.com/"}},{"title":"frontend-profesional","progress":24,"completed":false,"tutor":{"name":"Codemonkey ","link":"https://www.codemonkey.com/"}}]},
{"_id":"619","name":"Samantha","age":20,"sex":"F","courses":[{"title":"javascript-profesional","progress":35,"completed":false,"tutor":{"name":"Codeacademy","link":"codecademy.com"}},{"title":"android-profesional","progress":47,"completed":false,"tutor":{"name":"Code.org","link":"https://code.org/"}},{"title":"mongoose","progress":56,"completed":false,"tutor":{"name":"Codeacademy","link":"codecademy.com"}}]},
{"_id":"976","name":"Valentín","age":21,"sex":"M","courses":[{"title":"google-maps-flutter","progress":59,"completed":false,"tutor":{"name":"Codeacademy","link":"codecademy.com"}},{"title":"introduccion-nginx","progress":70,"completed":false,"tutor":{"name":"CódgioFacilito","link":"https://codigofacilito.com/"}},{"title":"javascript-profesional","progress":9,"completed":false,"tutor":{"name":"Codemonkey ","link":"https://www.codemonkey.com/"}}]},
{"_id":"534","name":"Valeria","age":25,"sex":"F","courses":[{"title":"angular-profesional","progress":97,"completed":false,"tutor":{"name":"Scratch","link":"https://scratch.mit.edu/"}},{"title":"introduccion-nginx","progress":5,"completed":false,"tutor":{"name":"Scratch","link":"https://scratch.mit.edu/"}},{"title":"javascript-profesional","progress":30,"completed":false,"tutor":{"name":"C├│dgioFacilito","link":"https://codigofacilito.com/"}},{"title":"nosql-moongodb","progress":52,"completed":false,"tutor":{"name":"Codemonkey ","link":"https://www.codemonkey.com/"}},{"title":"php-profesional","progress":6,"completed":false,"tutor":{"name":"Codemonkey ","link":"https://www.codemonkey.com/"}}]}
],
"bookmark": "g1AAAABCeJzLYWBgYMpgSmHgKy5JLCrJTq2MT8lPzkzJBYozmxqbgKQ4YFI5IMUgGfawxJzUoszERMmsLAA3nRFx",
"warning": "The number of documents examined is high in proportion to the number of results returned. Consider adding a more specific index to improve this."}

It doesn't show error but i'm no sure if this is a correct form, "use_index": "courses.title:age", "sort": ["name", "age"]

[wohali]

Looks right at a glance. Be sure to use the /{db}/_explain endpoint to double check that all is as expected.

[rjharmon]

It looks like it's doing a full index scan (over the docs that passed the defined partial_filter_selector, in name+age order) and warning that it checked many more docs than it returned (thus costing more I/O at the database, and some CPU for checking your conditions on every student who had one of the filtered courses). While technically it is "using" an index during query resolution, that doesn't mean you have gotten efficiency from it; as your db grows, you'd likely experience query slowdowns and rising CPU+I/O costs.

If the partial_filter_selector is eliminating a large number of docs from index-inclusion, that would otherwise have caused your query to be MUCH MORE expensive, then it may be giving you good-enough leverage against the total volume of docs in your db, and the warning (and I/O+CPU consumption) may be of secondary importance. That's your call based on expected volume & usage patterns.

Looking at your definitions again, I'd look at using a map/reduce index (instead of a mango index+query) on course-title (or id), then query the intended course value directly in that index - thus resolving your query with a high-efficiency index-range-scan even if your data volume grows a lot.

I'd still like to see Mango be able to resolve a query like this using an index-range-scan (perhaps by supporting courses[].title or similar expression in the mango index definition, given corresponding selectivity expressed in the mango query).

[hamiltonsalazar]

@rjharmon Do you have any example where i can read about using a map/reduce index?
I read the documentation but i only remember that i can create "map/reduce" for integer fields and how using "sum", "count" and others.

I'm new in CouchDB and the documentation doesn't have many options or examples for that.

[rjharmon]

You're right, one sometimes has to piece together bits from different places to arrive at working results for a specific case.

Here's an example of a map & reduce for indexing attributes {id, name} in an array of THINGS in some documents. These attributes need to be included in a design-doc view.

{
  map: function(doc) {
    if (doc.type === "widget" && doc.THINGS) {
      for (var i=0; i<doc.THINGS.length; i++) {
        var thing = doc.THINGS[i];
        emit([thing.id, thing.name], 1);
      }
    }
  },
  reduce: "_count",
...
}

Querying it based on some thingId could use view-querying options such as those shown here, with the unicode character indicating a hard end to the key range.

    { 
      include_docs: true,
      reduce: false,
      startkey: [thingId],
      endkey: [thingId, `\ufff0`]
    }
  }

See https://docs.couchdb.org/en/3.1.0/api/ddoc/views.html for more about querying.

[garrensmith]

When you use a partial_filter_selector you either have to do use_index when doing a query or you have to include the whole partial_filter_selector in the selector query along with any other selector fields you want to do. Otherwise mango cannot use that index.