Question 117280 in Mongodb Question posted in
The official documentation can be found here.

Mongodb – fetch elements from 3x nested array avoiding $unwind

I have the following document type:

{
  "_id": 1,
  "_a": [
    {
      "_aId": {
        "CC": "CA"
      },
      "_p": [
        {
          "_pId": {
            "CC": "CA",
            "SN": 1
          },
          "s": {
            "c": {
              "t": [
                {
                  "sId": 1,
                  "language": "CA",
                  "format": "A4"
                },
                {
                  "sId": 2,
                  "language": "JP",
                  "format": "A4"
                }
              ]
            },
            "a": {
              "t": [
                {
                  "sId": 4,
                  "language": "CA",
                  "format": "A4"
                },
                {
                  "sId": 5,
                  "language": "EN",
                  "format": "A3"
                }
              ]
            },
            "d": {
              "t": [
                {
                  "sId": 10,
                  "language": "CA",
                  "format": "A4"
                }
              ]
            }
          }
        }
      ]
    }
  ]
}

And I need faster solution to extract all subdocuments _aId, sId & _pid for language:"CA" & format:"A4" for documents with "_a._p._pid.CC":"CA"

The expected output is as follow:

{_aId:{"CC": "CA"},_pid:{CC:"CA",SN:1},sId:1}
{_aId:{"CC": "CA"},_pid:{CC:"CA",SN:1},sId:4}
{_aId:{"CC": "CA"},_pid:{CC:"CA",SN:1},sId:10}

Very similar to what @Turvishal and @Nimrod helped to solve here
But not very sure how to add also the _aId in the final output…

@Turvishal/@Nimrod solution for the previous part was excellent in the playground here , helped to avoid the heavy $unwind that I have used before , but now need to include the _aId from the root array which seems abit complex without $unwind , please, help?

( mongodb sharded cluster 4.2 )

Tags:

2

Answers


  1. 0

    Based on previous solutions, we just need to add it to the second step using $mergeObjects:

    db.collection.aggregate([
  {$match: {"_a._p._pId.CC": "CA"}},
  {$project: {
      _id: 0,
      _aId: "$_a._aId",
      _a: {
        $reduce: {
          input: "$_a",
          initialValue: [],
          in: {$concatArrays: [
              "$$value",
              {$map: {
                  input: {
                    $filter: {
                      input: "$$this._p",
                      cond: {$eq: ["$$this._pId.CC", "CA"]}}
                  },
                  as: "i",
                  in: {$mergeObjects: ["$$i", {_aId: "$$this._aId"}]}
                }
              }
            ]
          }
        }
      }
    }
  },
  {$project: {
      _a: {$reduce: {
          input: "$_a",
          initialValue: [],
          in: {$concatArrays: [
              "$$value",
              {$map: {
                  input: {
                    $reduce: {
                      input: {$objectToArray: "$$this.s"},
                      initialValue: [],
                      in: {$concatArrays: [
                          "$$value",
                          {$filter: {
                              input: "$$this.v.t",
                              cond: {$and: [
                                  {$eq: ["$$this.format", "A4"]},
                                  {$eq: ["$$this.language", "CA"]}
                                ]
                              }
                            }
                          }
                        ]
                      }
                    }
                  },
                  as: "p",
                  in: {_aId: "$$this._aId", _pId: "$$this._pId", sId: "$$p.sId"}
                }
              }
            ]
          }
        }
      }
    }
  },
  {$unwind: "$_a"},
  {$replaceRoot: {newRoot: "$_a"}}
])

    See how it works on the playground example

    The last unwind is only needed if you want to break the document into several documents as in your format. If you want to keep them all in one result document, you can ignore the last two phases.

    Login or Signup to reply.
  2. 0

    Query

    • 3 level nested map
    • get p arrays (concat s.c.t s.a.t s.d.t) (after filter on them)
    • and on return from each $map reduce to flatten the arrays
    • unwind and replace root

    *query is big, because 3 nested map, and with conditions in them
    but its a nested solution way without the unwind (unwind is used only when all docs are found to replace root with them)

    *on the right side its the cmql version which is the one i realy typed(mql was generated), its much smaler it might help on understanding the query

    *if we take advantage of the paths, like _a._p.s.c.t it might get smaller but with paths we lose which belongs to which, and i am not sure its possible

    Playmongo

    aggregate(
[{"$match": {"_a._p._pId.CC": {"$eq": "CA"}}},
 {"$set": 
   {"adocs": 
     {"$reduce": 
       {"input": 
         {"$map": 
           {"input": "$_a",
            "as": "out1",
            "in": 
             {"$reduce": 
               {"input": 
                 {"$map": 
                   {"input": "$$out1._p",
                    "as": "out2",
                    "in": 
                     {"$filter": 
                       {"input": 
                         {"$map": 
                           {"input": 
                             {"$concatArrays": 
                               ["$$out2.s.a.t", "$$out2.s.c.t", "$$out2.s.d.t"]},
                            "in": 
                             {"$cond": 
                               [{"$and": 
                                   [{"$eq": ["$$this.language", "CA"]},
                                     {"$eq": ["$$this.format", "A4"]}]},
                                 {"$mergeObjects": 
                                   ["$$this",
                                     {"_aId": "$$out1._aId", "_pId": "$$out2._pId"}]},
                                null]}}},
                        "cond": {"$ne": ["$$this", null]}}}}},
                "initialValue": [],
                "in": {"$concatArrays": ["$$value", "$$this"]}}}}},
        "initialValue": [],
        "in": {"$concatArrays": ["$$value", "$$this"]}}}}},
 {"$unwind": "$adocs"}, {"$replaceRoot": {"newRoot": "$adocs"}}])
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search