Need query help to assign a value as key in the aggregation query

[phanivikranth]

I am trying to write a mongo query to assign a key name as a value of another key.
query is given below.
db.getCollection(“test”).aggregate({$match:{type:{$in:[“test1”,“test2”,“test3”,“test4”,“test5”]}}},
{$project:{_id:0,name:1,value:1,type:1}},
{$group:{_id:“$type”,variable:{$push:{name:“$name”,value:“$value”,type:“$type”}}}});

the query above is creating the response as given below.

{
“_id” : “test1”,
“variable” : [
{
“name” : “name11”,
“value” : “name11”,
“type” : “test1”
},
{
“name” : “name12”,
“value” : “name12”,
“type” : “test1”
}
]
}
{
“_id” : “test2”,
“variable” : [
{
“name” : “name21”,
“value” : “name21”,
“type” : “test2”
},
{
“name” : “name22”,
“value” : “name22”,
“type” : “test2”
}
]
}
{
“_id” : “test3”,
“variable” : [
{
“name” : “name31”,
“value” : “name31”,
“type” : “test3”
},
{
“name” : “name32”,
“value” : “name32”,
“type” : “test3”
}
]
}
{
“_id” : “test4”,
“variable” : [
{
“name” : “name41”,
“value” : “name41”,
“type” : “test4”
},
{
“name” : “name42”,
“value” : “name42”,
“type” : “test4”
}
]
}
{
“_id” : “test5”,
“variable” : [
{
“name” : “name51”,
“value” : “name51”,
“type” : “test5”
},
{
“name” : “name52”,
“value” : “name52”,
“type” : “test5”
}
]
}

I want to make the response as given below.

{
“_id” : “test1”,
“test1” : [
{
“name” : “name11”,
“value” : “name11”,
“type” : “test1”
},
{
“name” : “name12”,
“value” : “name12”,
“type” : “test1”
}
]
}
{
“_id” : “test2”,
“test2” : [
{
“name” : “name21”,
“value” : “name21”,
“type” : “test2”
},
{
“name” : “name22”,
“value” : “name22”,
“type” : “test2”
}
]
}
{
“_id” : “test3”,
“test3” : [
{
“name” : “name31”,
“value” : “name31”,
“type” : “test3”
},
{
“name” : “name32”,
“value” : “name32”,
“type” : “test3”
}
]
}
{
“_id” : “test4”,
“test4” : [
{
“name” : “name41”,
“value” : “name41”,
“type” : “test4”
},
{
“name” : “name42”,
“value” : “name42”,
“type” : “test4”
}
]
}
{
“_id” : “test5”,
“test5” : [
{
“name” : “name51”,
“value” : “name51”,
“type” : “test5”
},
{
“name” : “name52”,
“value” : “name52”,
“type” : “test5”
}
]
}

basically the variable in the above has to changed to the output of the _id. so key name should be changed according to the value.

[steevej (Steeve Juneau)]

Please read Formatting code and log snippets in posts and format your code and sample document appropriately.

As a better alternative, please create a playground.

This will help us help you.

Thanks

[phanivikranth]

@phanivikranth

db.getCollection(“test”).aggregate({$match:{type:{$in:[“test1”,“test2”,“test3”,“test4”,“test5”]}}},
{$project:{_id:0,name:1,value:1,type:1}},
{$group:{_id:“$type”,variable:{$push:{name:“$name”,value:“$value”,type:“$type”}}}});

That’s for the input. Sorry i am new to this format, still learning I have given the playground here.

Mongo playground

What i want as response is in place of the “variable”, it should be populated with value of the _id.

{
“_id”: “test1”,
“test1”: [
{
“name”: “name11”,
“type”: “test1”,
“value”: “name11”
},
{
“name”: “name12”,
“type”: “test1”,
“value”: “name12”
}
]
}

[steevej (Steeve Juneau)]

1. It is usually not recommended to have “value” as field names.
2. It should be quite easy to do that last transformation in any languages.
3. Having the data server performs “cosmetic” manipulation adds processing that is easier to scale elsewhere in the front end or middle ware.

But it can be done. And the simplest logic I could think of is ugly and I would not implement that personally. See this playground.

Also look at $setField, may be it could be used.

[phanivikranth]

Hi Steeve,

Thanks for the solution, I have already implemented this transformation both in the front end, middle ware. I wanted to see the through put if it is implemented on the backend. Even in the backend i have implemented with the query given below. I just wanted to check if there is any better approach which i am missing. Thanks for the inputs & points. They are very valuable.

db.collection.aggregate([
{
$group: {
_id: “$type”,
items: {
$push: {
name: “$name”,
type: “$type”,
value: “$value”
}
}
}
},
{
$project: {
_id: 1,
dynamicField: {
$arrayToObject: [
[
{
k: “$_id”,
v: “$items”
}
]
]
}
}
},
{
$replaceRoot: {
newRoot: {
$mergeObjects: [
{ _id: “$_id” },
“$dynamicField”
]
}
}
}
])

Thanks,
Phanivikranth

[Asya_Kamsky]

A couple of points.

First, remove the $project stage before $group as it is doing nothing useful and can only cause issues in the future (the optimizer already knows which fields $group is using and will only read those fields from the document, so this stage is “redundant” to what already happens.

Second, to transform the output from $group into what I think you are describing, just add this stage:

{
  "$replaceWith": {
      $arrayToObject: [
         [
           {
               k: "$_id",
               v: "$variable"
           }
         ]
      ]
   }
}

This will result in something like this:

  {
    "test1": [
    {
      "name": "name11",
      "type": "test1",
      "value": "name11"
    },
    {
      "name": "name12",
      "type": "test1",
      "value": "name12"
    }
  ]
}, // etc

If you want to keep the $_id field just include that as a k, v pair as well. There is no reason to have all these stages, I don’t think they make things more readable, just construct the object you want to end up with in a single $replaceWith stage by having all the desired k, v elements in the array being passed to $arrayToObject.

Asya