Change object into array in key-value collection

Question

I've got a document in MongoDB like this:

{
  "name": "wine",
  "foodstuffSelectedPortions": {
    "id_1": [
      {
        "foodstuffId": "f1",
        "portion": {
          "portionName": "portion name 1",
          "portionWeight": {
            "value": 1,
            "unit": "KG"
          }
        }
      },
      {
        "foodstuffId": "f2",
        "portion": {
          "portionName": "portion name 2",
          "portionWeight": {
            "value": 100,
            "unit": "ML"
          }
        }
      }
    ],
    "id_2": [
      {
        "foodstuffId": "f3",
        "portion": {
          "portionName": "portion name 3",
          "portionWeight": {
            "value": 15,
            "unit": "ML"
          }
        }
      }
    ]
  }
}

and I want to update foodstuffSelectedPortions.portion object into array that contains this object. So the expected result should look like this:

{
  "name": "wine",
  "foodstuffSelectedPortions": {
    "id_1": [
      {
        "foodstuffId": "f1",
        "portion": [
          {
            "portionName": "portion name 1",
            "portionWeight": {
              "value": 1,
              "unit": "KG"
            }
          }
        ]
      },
      {
        "foodstuffId": "f2",
        "portion": [
          {
            "portionName": "portion name 2",
            "portionWeight": {
              "value": 100,
              "unit": "ML"
            }
          }
        ]
      }
    ],
    "id_2": [
      {
        "foodstuffId": "f3",
        "portion": [
          {
            "portionName": "portion name 3",
            "portionWeight": {
              "value": 15,
              "unit": "ML"
            }
          }
        ]
      }
    ]
  }
}

I've tried this query:

db.foodstuff.update(
{ },
{ $set: { "foodstuffSelectedPortions.$[].portion": ["$foodstuffSelectedPortions.$[].portion"] } }
)

but it gives me an error: Cannot apply array updates to non-array element foodstuffSelectedPortions: which looks fine because the foodstuffSelectedPortions is an object not array.

How to write this query correctly? I use MongoDB 4.4.4 and Mongo Shell.

The id_1 , id_2 i guess are not know schema right? they are data and you can have unknown number of them? like id_25 etc or you always have exactly 2 with names id_1 and id_2 ? If you store data on keys, i think its best to change your schema, to something like this If portion will contain more than 1 members then make the portion array of documents, else document is fine. Think of it based on your needs. — Takis
– Takis, Commented Sep 24, 2021 at 13:59
OK, I'm fine with changing the schema as you described, but I need the query to do that because I've got a lot of data and manually changing is not an option. — kojot
– kojot, Commented Sep 24, 2021 at 16:37

Takis · Accepted Answer · 2021-09-24 18:08:30Z

Query (the one you asked)
(do updateMany to update all to the new schema)

converts to array
map on array
map on nested array, replacing portion object with array

Test code here

db.collection.update({},
[
  {
    "$set": {
      "foodstuffSelectedPortions": {
        "$arrayToObject": {
          "$map": {
            "input": {
              "$objectToArray": "$foodstuffSelectedPortions"
            },
            "in": {
              "k": "$$f.k",
              "v": {
                "$map": {
                  "input": "$$f.v",
                  "in": {
                    "$mergeObjects": [
                      "$$f1",
                      {
                        "portion": [
                          "$$f1.portion"
                        ]
                      }
                    ]
                  },
                  "as": "f1"
                }
              }
            },
            "as": "f"
          }
        }
      }
    }
  }
])

Query (alternative schema with 2 levels of nesting instead of 3, and without data on keys)

An example why data on keys is bad(i guess there are exceptions), is why you are stuck, you wanted to update all, but you didn't know their names so you couldn't select them. If you had them in array you could do a map on all or you could use update operator to do update in all members of an array (like the way you tried to do it, and complained that is object)

*But dont use this schema unless it fits your data and your queries better.

Test code here

db.collection.update({},
[
  {
    "$set": {
      "foodstuffSelectedPortions": {
        "$reduce": {
          "input": {
            "$objectToArray": "$foodstuffSelectedPortions"
          },
          "initialValue": [],
          "in": {
            "$let": {
              "vars": {
                "f": "$$this"
              },
              "in": {
                "$concatArrays": [
                  "$$value",
                  {
                    "$map": {
                      "input": "$$f.v",
                      "in": {
                        "$mergeObjects": [
                          "$$f1",
                          {
                            "name": "$$f.k",
                            "portion": [
                              "$$f1.portion"
                            ]
                          }
                        ]
                      },
                      "as": "f1"
                    }
                  }
                ]
              }
            }
          }
        }
      }
    }
  }
])

Pipeline updates require MongoDB >=4.2

Collectives™ on Stack Overflow

Change object into array in key-value collection

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