CouchDB indexes to connect the dots between documents

Question

I have the following documents:

{ _id: "123", type: "project", worksite_id: "worksite_1" }
{ _id: "456", type: "document", project_id: "123" }
{ _id: "789", type: "signature", document_id: "456" }

My goal is to run a query and to inevitably do a filtered replication of all documents that have a connection with worksite_id: worksite_1.

Example:

1. Because this project has the worksite I am looking for
2. document has that project
3. signature has that document

I should be able to retrieve all of these documents if I want everything from that worksite.

Normally I would just add a worksite_id to my type:document and type:signature. However, worksite's can change in a project for various reasons.

I was wondering if there is a way to create an index or do something I am not thinking about to show these resemblances.

This feels like it is on the right path but the explanation puts documents inside other documents where I just want them to be separate.

Answer 1

A map function only considers one document at a time, so unless that document knows about other documents, you can't link them together. Your structure implies a three-table join in SQL terms.

With your structure, the best you can hope for is a two-request solution. You can create a view that shows signed documents only:

function (doc) {
  if (doc && doc.type && doc.type === "signature" && doc.document_id) {
    emit(doc.document_id, {_id: doc.document_id})
  }
}

and using the same technique, link projects to documents -- but you can't get all three linked.