How to convert deeply nested array with one object to object in mongodb aggregation?

Question

The requirement is to convert all sales array properties to objects with their first elements' quantity value. The collection name is branches and the example data is below.

[
{
    "_id": 111,
    "recs": [
        {
            "date": "01-01-2021",
            "sales": [
                {
                    "quantity": {
                        "P": 14,
                        "K": 0,
                        "U": 0
                    }
                }
            ]
        },
        {
            "date": "02-01-2021",
            "sales": [
                {
                    "quantity": {
                        "P": 23,
                        "K": 0,
                        "U": 0
                    }
                }
            ]
        }
    ]
},
{
    "_id": 21,
    "recs": [
        {
            "date": "01-01-2021",
            "sales": [
                {
                    "quantity": {
                        "P": 11,
                        "K": 0,
                        "U": 0
                    }
                }
            ]
        },
        {
            "date": "02-01-2021",
            "sales": [
                {
                    "quantity": {
                        "P": 31,
                        "K": 0,
                        "U": 0
                    }
                }
            ]
        }
    ]
}]

The expected response is below.

[
{
    "_id": 111,
    "recs": [
        {
            "date": "01-01-2021",
            "sale": {
                "P": 14,
                "K": 0,
                "U": 0
            }
        }
    ]
}]

I tried $map, $addFields but no way I couldn't find a solution.

Yong Shun · Accepted Answer · 2022-02-12 09:18:33Z

1

In $map, you need:

$first to query the first sales.quantity object in recs.sales array.
$mergeObject to merge the current document in recs array with the document from (1).

db.collection.aggregate([
  {
    "$project": {
      _id: 1,
      recs: {
        "$map": {
          "input": "$recs",
          "in": {
            "$mergeObjects": [
              "$$this",
              {
                sales: {
                  $first: "$$this.sales.quantity"
                }
              }
            ]
          }
        }
      }
    }
  }
])

Sample Mongo Playground

answered Feb 12, 2022 at 9:18

Yong Shun

54.5k6 gold badges38 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

p_escobr Over a year ago

It works like a charm. I really appreciate you. Lastly, I'm just asking because I'm curious, Is it possible to make the same thing with $arrayToObject ?

Yong Shun Over a year ago

Hi, I think $arrayToObject is not suitable for this case.

Collectives™ on Stack Overflow

How to convert deeply nested array with one object to object in mongodb aggregation?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related