Adding field to nested document, with unknown field names $ObjectToArray

Question

I am using MongoDB 6.0 and mongosh 1.5. I have a collection, which have elements with following structure:

{
    "name": "Optimization using NN",
    "calculations": {
        "1": {
            "code": "A123",
            "purpose": "TIME",
            "cities": {
                "PARIS": {
                    "load": 50,
                    "price": 10.45
                },
                "ROME": {
                    "load": 100,
                    "price": 8.25
                },
                "LYON": {
                    "load": 40,
                    "price": 9.00
                }
            }
        },
        "2": {
            "code": "B123",
            "purpose": "COST",
            "cities": {
                "PARIS": {
                    "load": 150,
                    "price": 9.45
                },
                "ROME": {
                    "load": 40,
                    "price": 7.25
                },
                "LYON": {
                    "load": 30,
                    "price": 8.00
                }
            }
        }
    }
}

I need to add field to every calculation, which is not really dificult:

db.experiment.updateMany({},
    [{
            $set: {
                'calculations': {
                    $objectToArray: '$calculations'
                }
            }
        }, {
            $addFields: {
                'calculations.v.active': {
                    $literal: true
                }
            }
        }, {
            $set: {
                'calculations': {
                    $arrayToObject: '$calculations'
                }
            }
        }

    ])

Is there some way to add field "constraints" with value of empty object to all cities?

{
    "name": "Optimization using NN",
    "calculations": {
        "1": {
            "code": "A123",
            "purpose": "TIME",
            "cities": {
                "PARIS": {
                    "load": 50,
                    "price": 10.45,
                    "constraints" : {}
                },
                "ROME": {
                    "load": 100,
                    "price": 8.25,
                    "constraints" : {}
                },
                "LYON": {
                    "load": 40,
                    "price": 9.00,
                    "constraints" : {}
                }
            },
            "active" : true
        },
        "2": {
            "code": "B123",
            "purpose": "COST",
            "cities": {
                "PARIS": {
                    "load": 150,
                    "price": 9.45,
                    "constraints" : {}
                },
                "ROME": {
                    "load": 40,
                    "price": 7.25,
                    "constraints" : {}
                },
                "LYON": {
                    "load": 30,
                    "price": 8.00,
                    "constraints" : {}
                }
            },
            "active" : true
        }
    }
}

I can't use $objectToArray again on '$calculations.v.cities', because it is already an array.

calculations object can be an array i think in 2 ways, if indexes are always there, like 1,2,3,4... you dont need the keys, if indexes can be missing like 1,5,10 it can be still an array, with the index inside the embeded document, this doesnt answer your question, but maybe you decide to change schema, to make things easier. In general saving data in fields(data inside the schema) is very bad idea for mongodb. Query language is made to query known fields in general. — Takis
– Takis, Commented Jun 4, 2022 at 19:27
Understand. Unfortunately, these keys are rarely consecutive integers. Sometimes keys are like '1', '3', '4', '7', '8'. Some ordinals are ommitted. — DDT
– DDT, Commented Jun 4, 2022 at 19:37
ok even in this case you can still keep it as array, like [{"index": 5 ...} ...] and avoiding to have data on schema. Else you can keep this schema but i think its not good idea — Takis
– Takis, Commented Jun 4, 2022 at 20:04

Takis · Accepted Answer · 2022-06-04 21:58:00Z

Query

you have data in keys in nested way also, so here $ObjectToArray and back to array, is needed in a nested way
this produces the expected result, adds active : true in all calculations, and constraints : {} to all cities

*maybe query could be smaller, but in general queries that have data in the schema are complicated and slow, i suggest to change your schema

Playmongo

aggregate(
[{"$set": 
   {"calculations": 
     {"$map": 
       {"input": {"$objectToArray": "$calculations"},
        "as": "outer",
        "in": 
         {"k": "$$outer.k",
          "v": 
           {"$mergeObjects": 
             [{"active": true}, "$$outer.v",
               {"cities": 
                 {"$arrayToObject": 
                   [{"$map": 
                       {"input": {"$objectToArray": "$$outer.v.cities"},
                        "as": "inner",
                        "in": 
                         {"k": "$$inner.k",
                          "v": 
                           {"$mergeObjects": 
                             ["$$inner.v", {"constraints": {}}]}}}}]}}]}}}}}},
 {"$set": {"calculations": {"$arrayToObject": ["$calculations"]}}}])

Collectives™ on Stack Overflow

Adding field to nested document, with unknown field names $ObjectToArray

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related