I have a database as below.

[
    {
        "title": "Data 1",
        "slug": "data-1",
        "sub_docs": [
            {
                "name": "Sub Doc 1",
                "sub_slug": "sub-doc-1",
                "urls": [
                    "https://example.com/path-1",
                ]
            },
            {
                "name": "Sub Doc 2",
                "sub_slug": "sub-doc-2",
                "urls": [
                    "https://example.com/path-2"
                ]
            },
            {
                "name": "Sub Doc 3",
                "sub_slug": "sub-doc-3",
                "urls": [
                    "https://example.com/path-3",
                ]
            }
        ]
    }
]

When I give data-1 and sub-doc-2 as input, I want to get the following result.

{
    "title": "Data 1",
    "sub_doc_title": "Sub Doc 2",
    "urls": [
        "https://example.com/path-2"
    ],
    "prev": {
        "name": "Sub Doc 1",
        "sub_slug": "sub-doc-1"
    },
    "next": {
        "name": "Sub Doc 3",
        "sub_slug": "sub-doc-3"
    }
}

I'm a bit of a beginner at MongoDB. How can I do this query? Do you have an idea?

CodePudding user response：

That's a tough query for beginner, although I still consider myself a novice so maybe there's a better/easy way.

Here's one way you could do it.

db.collection.aggregate([
  {
    "$match": {
      "slug": "data-1"  // given argument
    }
  },
  {
    "$set": {
      "reduceOut": {
        "$reduce": {
          "input": "$sub_docs",
          "initialValue": {
            "sub_doc_title": null,
            "urls": [],
            "prev": null,
            "next": null
          },
          "in": {
            "$cond": [
              {
                "$eq": [
                  "$$this.sub_slug",
                  "sub-doc-2"         // given argument
                ]
              },
              {
                "$mergeObjects": [
                  "$$value",
                  {
                    "sub_doc_title": "$$this.name",
                    "urls": "$$this.urls"
                  }
                ]
              },
              {
                "$cond": [
                  { "$eq": [ "$$value.sub_doc_title", null ] },
                  {
                    "$mergeObjects": [
                      "$$value",
                      {
                        "prev": {
                          "name": "$$this.name",
                          "sub_slug": "$$this.sub_slug"
                        }
                      }
                    ]
                  },
                  {
                    "$cond": [
                      { "$eq": [ "$$value.next", null ] },
                      {
                        "$mergeObjects": [
                          "$$value",
                          {
                            "next": {
                              "name": "$$this.name",
                              "sub_slug": "$$this.sub_slug"
                            }
                          }
                        ]
                      },
                      "$$value"
                    ]
                  }
                ]
              }
            ]
          }
        }
      }
    }
  },
  {
    "$replaceWith": {
      "$mergeObjects": [
        {
          "title": "$title"
        },
        "$reduceOut"
      ]
    }
  }
])

Try it on mongoplayground.net.

CodePudding user response：

Thanks rickhg12hs for your answer. I also found a solution to this question.

Here is my answer;

db.collection.aggregate([
  {
    "$match": {
      "slug": "data-1"
    }
  },
  {
    "$addFields": {
      "sub_doc_index": {
        "$indexOfArray": [
          "$sub_docs.sub_slug",
          "sub-doc-2"
        ]
      }
    }
  },
  {
    "$project": {
      "title": 1,
      "sub_doc_title": {
        "$arrayElemAt": [
          "$sub_docs.name",
          "$sub_doc_index"
        ]
      },
      "urls": {
        "$arrayElemAt": [
          "$sub_docs.urls",
          "$sub_doc_index"
        ]
      },
      "prev": {
        "$cond": {
          "if": {
            "$eq": [
              {
                "$subtract": [
                  "$sub_doc_index",
                  1
                ]
              },
              -1
            ]
          },
          "then": {},
          "else": {
            "name": {
              "$arrayElemAt": [
                "$sub_docs.name",
                {
                  "$subtract": [
                    "$sub_doc_index",
                    1
                  ]
                }
              ]
            },
            "slug": {
              "$arrayElemAt": [
                "$sub_docs.sub_slug",
                {
                  "$subtract": [
                    "$sub_doc_index",
                    1
                  ]
                }
              ]
            }
          },
          
        },
        
      },
      "next": {
        "name": {
          "$arrayElemAt": [
            "$sub_docs.name",
            {
              "$add": [
                "$sub_doc_index",
                1
              ]
            }
          ]
        },
        "slug": {
          "$arrayElemAt": [
            "$sub_docs.sub_slug",
            {
              "$add": [
                "$sub_doc_index",
                1
              ]
            }
          ]
        }
      }
    }
  },
  
])