Home > Net >  Mongo Query that coverts keys from subdicts into key:value pairs
Mongo Query that coverts keys from subdicts into key:value pairs

Time:08-24

I'm new to Mongo, and I have a collection of documents that look like this:

[
  {
    "group": "Alpha"
    "test_1": {
      "description": "test description",
      "value": 1
    },
    "test_2": {
      "description": "test description",
      "value": 2
    },
    "test_3": {
      "description": "test description",
      "value": 3
    },
    "test_4": {
      "description": "test description",
      "value": 4
    },
    "test_5": {
      "description": "test description",
      "value": 5
    }
  }
]

The group key always exists. I don't know the real names of any other top level keys. These could be anything, but will always contain a dict with a description and value. I need the data output in this format:

  {
    "group": "Alpha"
    "test_1": 1,
    "test_2": 2,
    "test_3": 3,
    "test_4": 4,
    "test_5": 5
  }

Is this achievable within a query or do I need to post-process it?

CodePudding user response:

  1. $set - Create kv field.

    1.1. $reduce - Iterate array element and transform to a new value.

    1.1.1. input: With $objectToArray, transform $$ROOT document from key-value pair to array of objects with k and v properties.

    1.1.2. initialValue: Initialize the outcome as an empty array.

    1.1.3. in: $concatArrays - Merge accumulator $$value with the array from 1.1.3.1.

    1.1.3.1. $cond - Check the value of k from the current iterated document, and return the respective document value.

  2. $replaceWith - Replace the input document with key-value pair which is converted from kV field via $arrayToObject.

db.collection.aggregate([
  {
    $set: {
      kv: {
        $reduce: {
          input: {
            $objectToArray: "$$ROOT"
          },
          initialValue: [],
          in: {
            $concatArrays: [
              "$$value",
              [
                {
                  $cond: {
                    if: {
                      $eq: [
                        "$$this.k",
                        "group"
                      ]
                    },
                    then: {
                      k: "$$this.k",
                      v: "$$this.v"
                    },
                    else: {
                      k: "$$this.k",
                      v: {
                        $ifNull: [
                          "$$this.v.value",
                          "$$this.v"
                        ]
                      }
                    }
                  }
                }
              ]
            ]
          }
        }
      }
    }
  },
  {
    $replaceWith: {
      $arrayToObject: "$kv"
    }
  }
])

Demo @ Mongo Playground

  • Related