Complex queries in CouchDB across multiple documents

Question

I'm relatively new to CouchDB (more specifically Cloudant if it matters) and I'm having a hard time wrapping my head around something.

Assume the following (simplified) document examples:

{ "docType": "school", "_id": "school1", "state": "CA" }
{ "docType": "teacher", "_id": "teacher1", "age": "40", "school": "school1" }

I want to find all the teachers aged 40 in California.

Maybe my mind is still stuck on SQL world, but I'm having a hard time understanding how to run a query that uses data from different documents...

PS. let me know if this is more suited to StackOverflow...

score 0 · Answer 1 · answered Feb 13 '20 at 17:16

0

For quick one time queries I would use _find, for permanent ones where performance is important I would create a view for this.

Views are very well explained in the booklet "Writing and Querying MapReduce Views in CouchDB: Tools for Data Analysts".

answered Feb 13 '20 at 17:16

gdelfino

103
3

score 0 · Answer 2 · answered Dec 12 '23 at 15:22

You can join on related documents within the same database (see CouchDB documentation topic: Joins With Views - Linked Documents).

The documents from the question:

{ "docType": "school", "_id": "school1", "state": "CA" }
{ "docType": "teacher", "_id": "teacher1", "age": "40", "school": "school1" }

For this create a view, which allows filtering on the main document. The view has a map function which does the join:

function (doc) {
  if (doc.school && doc.age === 40) {
    emit([ doc._id, doc.age ], { _id: doc.school });
  }
}

Note the filter on the age field of the teacher document.

This view is to be queried using the parameter include_docs=true (this parameter is required to get the joined document details), for example:

curl -X GET http://usr:<pwd>@127.0.0.1:5984/my_db/_design/my_design/_view/my_view?include_docs=true

For example, the output:

{
   "total_rows":1,
   "offset":0,
   "rows":[
      {
         "id":"teacher1",
         "key":[
            "teacher1",
            "40"
         ],
         "value":{
            "_id":"school11"
         },
         "doc":{
            "_id":"school1",
            "_rev":"1-0be327d3b2c9d07a4f143d93067b46c6",
            "docType":"school",
            "state":"CA"
         }
      }
   ]
}

This JSON result can be further processed / filtered for the schools in the state of "CA" (in an application).

Complex queries in CouchDB across multiple documents

2 Answers2