How do I project sub elements in a Mongo query?

Question

I'm trying to pull specific sub elements from a Mongo database. I've used a sample set of data containing all the books and verses in the Bible. I need to query and display an individual verse.

The data looks like this:

{"book": "1 Chronicles",
    "chapters": [
           {"chapter": "1", 
                 "verses": [
                     {"verse": "1", "text": "Adam, Sheth, Enosh,"}, 
                     {"verse": "2", "text": "Kenan, Mahalaleel, Jered,"}]},
           {"chapter": "2", 
                 "verses": [
                    {"verse": "1", "text": "These are the sons of Israel; Reuben, Simeon, Levi,"}, 
                    {"verse": "2", "text": "Dan, Joseph, and Benjamin, Naphtali, Gad, and Asher."}]}, 
           {"chapter": "3", 
                  "verses": [
                    {"verse": "1", "text":  “etc etc…..”}]}]
             }

I've tried many query variations but nothing seems to work. Here are two I've tried:

Db.bible.find({“book”:”1 Chronicles”,”chapters.chapter”:”1”,”verses.verse”:”1”}).pretty()

Db.bible.find({“book”:”1 Chronicles”,”chapters”:[“chapter”:”1”, “verses”:[“verse”:”1”]]})

I have a background in MySQL, so maybe I'm just looking at this the wrong way?

Answer 1

This is "array filtering" on more than a single match which the basic projection capabilities of .find() cannot do.

You can use MongoDB aggregation framework to achieve your use case.

MongoDB’s aggregation framework is modeled on the concept of data processing pipelines. Documents enter a multi-stage pipeline that transforms the documents into an aggregated result.

You can use $project and $filter in an aggregation pipeline to get your end result.

db.collection.aggregate([
  {
    $match: {
      "book": "1 Chronicles"
    }
  },
  {
    "$project": {
      "chapters": {
        "$filter": {
          "input": {
            "$map": {
              "input": "$chapters",
              "as": "chapters",
              "in": {
                "chapter": "$$chapters.chapter",
                "verses": {
                  "$filter": {
                    "input": "$$chapters.verses",
                    "as": "verses",
                    "cond": {
                      "$eq": [
                        "$$verses.verse",
                        "1"
                      ]
                    }
                  }
                }
              }
            }
          },
          "as": "chapters",
          "cond": {
            "$eq": [
              "$$chapters.chapter",
              "1"
            ]
          }
        }
      }
    }
  }
])

MongoDB Playground