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 )

CodePudding user response：

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.

CodePudding user response：

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"}}])