Records not getting retrieved in case of indexed array.

[shrikantgarg17]

Hi Team,

We are facing issues while retrieving records from an indexed array. though we are not sure if indexing is wrong or querying is wrong but it is taking lot of time to get data.
We are using Couch DB. Here are the details.

{
    "couchdb": "Welcome",
    "version": "3.1.0",
    "git_sha": "ff0feea20",
    "uuid": "c371bc5f-ad93-11eb-b1fa-968ae7512ef5",
    "features": [
        "access-ready",
        "partitioned",
        "pluggable-storage-engines",
        "reshard",
        "scheduler"
    ],
    "vendor": {
        "name": "XXXXX"
    }
}

We are storing our records in one database whose structure is like this

{
    "_id": "Test:1a90228b1a6347af9452b51c549c3a49",
    "_rev": "1-1908817280c7fec3dfdd9982c680bee7",

    "shri": {
        "tags": [
            "java"
        ],
        "Country": [
            "US"
        ],
        "status": "compliant"
    },
    "tags": {},

    "status": "active",

    "createTime": 1633684718741,
    "modifyTime": 0
}

so i want to have index on tags attribute which is an array. below are the index which i have created

{
    "_id": "index1",
    "_rev": "1-56cbb4c9768fd34749b89dfffd446bf7",
    "language": "query",
    "views": {
        "tags-json-index": {
            "map": {
                "fields": {
                    "shri.tags": "asc",
                    "_id": "asc"
                },
                "partial_filter_selector": {}
            },
            "reduce": "_count",
            "options": {
                "def": {
                    "fields": [{
                            "shri.tags": "asc"
                        },
                        {
                            "_id": "asc"
                        }
                    ]
                }
            }
        }
    }
}

and my selector query is :

{
    "selector": {
        "$and": [{
            "shri.tags": {
                "$elemMatch": {
                    "$eq": "xyz"
                }
            }
        }, {
            "_id": {
                "$gte": "0"
            }
        }]
    },
    "fields": ["_id", "shri"],
    "sort": [{
        "_id": "asc"
    }],
    "limit": 501
}

but my query takes lot of time. Not sure if indexing is working properly or not.

Below is snippet when i explain my query:

{
    "dbname": "oc-xx-xyz",
    "index": {
        "ddoc": null,
        "name": "_all_docs",
        "type": "special",
        "def": {
            "fields": [{
                "_id": "asc"
            }]
        }
    },
    "partitioned": "undefined",
    "selector": {
        "$and": [{
                "shri.tags": {
                    "$elemMatch": {
                        "": {
                            "$eq": "xyz"
                        }
                    }
                }
            },
            {
                "_id": {
                    "$gte": "0"
                }
            }
        ]
    },
    "opts": {
        "use_index": [],
        "bookmark": "nil",
        "limit": 501,
        "skip": 0,
        "sort": {
            "_id": "asc"
        },
        "fields": [
            "_id",
            "shri"

        ],
        "partition": "",
        "r": [
            49
        ],
        "conflicts": false,
        "stale": false,
        "update": true,
        "stable": false,
        "execution_stats": false
    },
    "limit": 501,
    "skip": 0,
    "fields": [
        "_id",
        "shri"

    ],
    "mrargs": {
        "include_docs": true,
        "view_type": "map",
        "reduce": false,
        "partition": null,
        "start_key": "0",
        "end_key": "",
        "direction": "fwd",
        "stable": false,
        "update": true,
        "conflicts": "undefined"
    }
}

Can anyone please help me on this.

Thanks

[big-r81]

Hi,

I updated your comment for better readability of the json source. Please use code blocks.

Your query isn't using your index, which is shown:

"index": {
        "ddoc": null,
        "name": "_all_docs",
        "type": "special",
        "def": {
            "fields": [{
                "_id": "asc"
            }]
        }
    },

It's using the _all_docs endpoint for quering your results.

Did you try to explicitly use use_index in your _find query to use your index?

[shrikantgarg17]

Yeah.. that is the problem.. even when i use use_index in my query it is not picking my index and always using _all_docs. Not sure what is the probelm.

[willholley]

@shrikantgarg17 array elements are not indexed independently when using a json index (see #1388 for the explanation). At query time, CouchDB will ignore the $elemMatch operator when evaluating which indexes can be used. Given the only other clause in your selector is _id > 0, you end up with a full database scan.

As a solution, you can either use a view directly and emit each element in the index as a key, or add the search plugin and use the text type index, which can index array elements independently.

The view solution would be a map function like:

function(doc) {
    if(doc.shri && doc.shri.tags) {
        for (var i = 0; i <  doc.shri.tags.length; i++) { 
            emit(doc.shri.tags[i], null);
        }
    }
}

and then all the view using http://couchdburl/db/_design/my_ddoc/_view?key="xyz"

[big-r81]

I think the answer is much simpler:

There is a special default index only for _id which is for me:

{
  "total_rows": 2,
  "indexes": [
    {
      "ddoc": null,
      "name": "_all_docs",
      "type": "special",
      "def": { "fields": [{ "_id": "asc" }] }
    },
    {
      "ddoc": "_design/c3f8e5d6df2ef807ee1016949098ac4e6287d31c",
      "name": "tags-json-index",
      "type": "json",
      "partitioned": false,
      "def": { "fields": [{ "_id": "asc" }, { "shri": "asc" }] }
    }
  ]
}

If I only sort for _id the "special" index is used. If I use

  "sort" : ["_id", "shri"]

in the query, with the following ddoc

{
  "_id": "_design/c3f8e5d6df2ef807ee1016949098ac4e6287d31c",
  "_rev": "3-bbb25abe2218156af2ad8a5d6648ce1d",
  "language": "query",
  "views": {
    "tags-json-index": {
      "map": {
        "fields": {
          "_id": "asc",
          "shri": "asc"
        },
        "partial_filter_selector": {}
      },
      "reduce": "_count",
      "options": {
        "def": {
          "fields": [
            "_id",
            "shri"
          ]
        }
      }
    }
  }
}

then the the mango query uses this index...

{
 "dbname": "test",
 "index": {
  "ddoc": "_design/c3f8e5d6df2ef807ee1016949098ac4e6287d31c",
  "name": "tags-json-index",
  "type": "json",
  "partitioned": false,
  "def": {
   "fields": [
    {
     "_id": "asc"
    },
    {
     "shri": "asc"
    }
   ]
  }
 },
 "partitioned": false,
 "selector": {
  "$and": [
   {
    "shri.tags": {
     "$elemMatch": {
      "": {
       "$eq": "java"
      }
     }
    }
   },
   {
    "_id": {
     "$gte": "0"
    }
   }
  ]
 },
 "opts": {
  "use_index": [],
  "bookmark": "nil",
  "limit": 501,
  "skip": 0,
  "sort": {
   "_id": "asc",
   "shri": "asc"
  },
  "fields": [
   "_id"
  ],
  "partition": "",
  "r": [
   49
  ],
  "conflicts": false,
  "stale": false,
  "update": true,
  "stable": false,
  "execution_stats": false
 },
 "limit": 501,
 "skip": 0,
 "fields": [
  "_id"
 ],
 "mrargs": {
  "include_docs": true,
  "view_type": "map",
  "reduce": false,
  "partition": null,
  "start_key": [
   "0"
  ],
  "end_key": [
   "<MAX>"
  ],
  "direction": "fwd",
  "stable": false,
  "update": true,
  "conflicts": "undefined"
 }
}

But you are right, with your view function, you have the possibility to use a dynamic query for the tag...

[big-r81]

Moving to discussions...

[willholley]

ah I'd forgotten about the sort in my response. The suggested solution using Mango is still going to result in a full index scan (which is more resource hungry than scanning the _all_docs special index if the index contains all the documents in the database). You can see this behaviour by viewing the execution stats for the query. You'll also get a warning in Fauxton and the response body if the number of documents scanned was disproportionately high vs the result.

To add the sort requirement to the view, you would need to add the doc._id to the key (views are sorted on key order) and adjust the query to match on the first element of the key only:

function(doc) {
    if(doc.shri && doc.shri.tags) {
        for (var i = 0; i <  doc.shri.tags.length; i++) {
            var key = [doc.shri.tags[i], doc._id];
            emit(key, null);
        }
    }
}

and then query the view for all docs containing shri.tag of "xyz", sorted by doc._id, using http://couchdburl/db/_design/my_ddoc/_view?startkey=["xyz"]&endkey=["xyz",{}]