create a pre filter for _find queries

[khl3o]

To improve the query time response, I would like to know if it's possible to pre-filter the documents where for a _find query is applied :

Create an index like :

{
  "index": {
    "partial_filter_selector": {
      "type": {
        "$eq": "installation"
      },
      "contract.country": {
        "$eq": "us"
      }
    }
  },
  "ddoc" : "installation-us",
  "type" : "json"
}

So if I execute a query like

{
   "selector": {},
   "use_index": "installation-us"
}

I will only get the documents where doc.type = "installation" and doc.contract.country = "us". And the possibility to create a selector only on this specific documents (and apply the selector only on 5k instead of 500k documents).

[willholley]

I think you will need to include the partial_filter_selector in the selector passed to _find (in addition to your more discriminative selector) but otherwise this should work.

…

On Thu, 22 Dec 2022, 17:19 Paul Danilo, ***@***.***> wrote: To improve the query time response, I would like to know if it's possible to pre-filter the documents where for a _find query is applied : Create an index like : { "index": { "partial_filter_selector": { "type": { "$eq": "installation" }, "contract.country": { "$eq": "us" } } }, "ddoc" : "installation-us", "type" : "json" } So if I execute a query like { "selector": {}, "use_index": "installation-us" } I will only get the documents where doc.type = "installation" and doc.contract.country = "us". And the possibility to create a selector only on this specific documents (and apply the selector only on 5k instead of 500k documents). — Reply to this email directly, view it on GitHub <#4334>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAAX362HYBL6RZYIUAX2MHLWOSETBANCNFSM6AAAAAATG6IOIU> . You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>

[khl3o]

thanks for the answer, pre-calculate an index with the filtered documents (like a view) is not possible with couchDB ?

[willholley]

@khl3o it is possible using partial_filter_selector. Ignore my previous comment - your example should work, except that indexes require a "fields" array which, if you don't care about, you could just set to ["_id"]:

{
  "index": {
    "fields": [
      "_id"
    ],
    "partial_filter_selector": {
      "type": {
        "$eq": "installation"
      },
      "contract.country": {
        "$eq": "us"
      }
    }
  },
  "ddoc" : "installation-us",
  "type" : "json"
}