Whats the alternative to $replaceRoot on mongoDB? $replaceRoot is incompatible with documentDB

user_2763004 0
Viewed 741

The problem: I'm trying to make a query on MongoDB, but I'm using the DocumentDb from amazon, where some operations are no supported. I wanted to find an alternative to get the same result, if possible. Basically I want to change the root of the result, instead of being the first entity, I need it to be some merging of some values in different levels of the document.

So, I have the following structure in my collection:

{
    "_id" : ObjectId("5e598bf4d98f7c70f9aa3b58"),
    "status" : "active",
    "invoices" : [ 
        {
            "_id" : ObjectId("5e598bf13b24713f50600375"),
            "value" : 1157.52,
            "receivables" : [ 
                {
                    "situation" : {
                        "status" : "active",
                        "reason" : []
                    },
                    "rec_code" : "001",
                    "_id" : ObjectId("5e598bf13b24713f50600374"),
                    "expiration_date" : ISODate("2020-03-25T00:00:00.000Z"),
                    "value" : 1157.52
                }
            ],
            "invoice_code" : 9773,
            "buyer" : {
                "legal_name" : "test name",
                "buyer_code" : "223132165498797"
            }
        },
    ],
    "seller" : {
        "code" : "321654897986",
        "name" : "test name 2"
    }
}

What I want to achieve is to list all "receivables" like this, where the _id is the _id of the receivable:

[{
    "_id" : ObjectId("5e598bf13b24713f50600374"),
    "situation" : {
        "status" : "active",
        "reason" : []
    },
    "rec_code" : "001",
    "expiration_date" : ISODate("2020-03-25T00:00:00.000Z"),
    "value" : 1157.52,
    "status" : "active",
    "seller" : {
        "cnpj" : "321654897986",
        "name" : "test name 2"
    },
    "invoice_code" : 9773.0,
    "buyer" : {
        "legal_name" : "test name",
        "cnpj" : "223132165498797"
    }
}]

This I can do with $replaceRoot in with the query below on MongoDB, but using documentDB I can't use $replaceRoot or $mergeObjects. Do you know how can I get the same result with other operators?:

db.testCollection.aggregate([
   { $unwind: "$invoices" },
   { $replaceRoot: { 
       newRoot: {
           $mergeObjects: ["$$ROOT","$invoices"]}
       }
   },
   {$project: {"_id": 0, "value": 0, "created_at": 0, "situation": 0}},
   { $unwind: "$receivables" },
   { $replaceRoot: { 
       newRoot: {
           $mergeObjects: ["$receivables", "$$ROOT"]
           }
       }
   },
   {$project:{"created_at": 0, "receivables": 0, "invoices": 0}}
])
aws-documentdb-mongoapi mongodb
2 Answers

After going through mongodb operations, I could get a similar result fro what I wanted with the following query without $replaceRoot. It turns out it was a better query, I think:

db.testCollection.aggregate([
    {$unwind: "$invoices"},
    {$project : {
        created_at: 1,
        seller: "$seller",
        buyer: "$invoices.buyer",
        nnf: "$invoices.nnf",
        receivable: '$invoices.receivables'
        }
    },
    {$unwind: "$receivable"},
    {$project : {
        _id: '$receivable._id',
        seller: 1,
        buyer: 1,
        invoice_code: 1,
        receivable: 1,
        created_at: 1,
     }
    },
    {$sort: {"created_at": -1}},
 ])

This query resulted in the following structure list:

[{
    "created_at" : ISODate("2020-03-06T09:47:26.161Z"),
    "seller" : {
        "name" : "Test name",
        "cnpj" : "21231232131232"
    },
    "buyer" : {
        "cnpj" : "21322132164654",
        "legal_name" : "Test name 2"
    },
    "invoice_code" : 66119,
    "receivable" : {
        "rec_code" : "001",
        "_id" : ObjectId("5e601bb5efff82b92935bad4"),
        "expiration_date" : ISODate("2020-03-17T00:00:00.000Z"),
        "value" : 6540.7,
        "situation" : {
            "status" : "active",
            "reason" : []
        }
    },
    "_id" : ObjectId("5e601bb5efff82b92935bad4")
}]
user_27630040

Support for $replaceRoot was added to Amazon DocumentDB in January 2021.

user_772000
Related