fetch elements from 3x nested array avoiding $unwind

Question

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 )

nimrod serok · Accepted Answer · 2022-07-17 13:40:24Z

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.

Takis · Accepted Answer · 2022-07-17 15:03:05Z

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

i had some extra code, that i removed and answer got smaller, filter creteria for example was the same and i forgot it, this is smaller, but still its big 3x nested

Collectives™ on Stack Overflow

fetch elements from 3x nested array avoiding $unwind

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related