Converting rows to columns

Working with Data
1

I have a collection like this

{
  "_id": {
    "$oid": "63776ec45efa596fb04c6e8b"
  },
  "number": "102345890",
  "Result": "failure"
},
{
  "_id": {
    "$oid": "63776ec45efa596fb04c6e8b"
  },
  "number": "102245890",
  "Result": "failure"
},
{
  "_id": {
    "$oid": "63776ec45efa596fb04c6e8c"
  },
"number": "100345890",
  "Result": "success"
},
{
  "_id": {
    "$oid": "63776ec45efa596fb04c6e8f"
  },
  "number": "101345890",
  "Result": "success"
}

I want the output to be like below:

{
  
  "failednumbers":[ "102345890","102245890"]
  "successnumbers":["100345890","101345890"]
}

How do achieve this?I tried using groupby but i am not getting desired results

2

This is almost the same thing as your other thread Need to append values based on one column value - #5 by steevej.

The difference is that the _id of $group is $Result.

If you still have issues, share what you tried and explain how it fails to deliver the desired result.

1 Like
3

Hi @steevej ,
Thanks for the quick reply.
After using

reports_col.aggregate([{"$group": {"_id": "$Result", "number": {"$push": "$number"}}}, {"$project": { "Result":"$_id","number":1,"Reportgeneratedat":datetime.now(),"_id":0 }},{"$out": "updatedreports"}])

,my MongoDB collection looks like below:

mdb1
mdb11259×114 12.4 KB

But i want to column names to be like failednumbers and successnumbers with corresponding data below and I want to make my mongodb collection to look like below:

mdb2
mdb2954×197 20.5 KB

Thanks in advance

4

I think you simply need an extra $group along the lines of:

"$group" : {
  "_id" : null ,
  "failednumbers" : { "$push" : { "$each" : "$failure" } } ,
  "successnumbers" : { "$push" : { "$each" : "$success" } }
}
1 Like
5

Hi @steevej ,
Tried the above query ,and i am getting the following error

"Unrecognized expression '$each', full error: {'ok': 0.0, 'errmsg': \"Unrecognized expression '$each'\", 'code': 168, 'codeName': 'InvalidPipelineOperator'}"

My query,

 reports_col.aggregate([{"$group": {"_id": "$Result", "number": {"$push": "$number"}}},{"$group" : {"_id" : "null" ,"failednumbers" : { "$push" : { "$each" : "$failure" } } ,"successnumbers" : { "$push" : { "$each" : "$success" } }}},{"$project": { "Result":"$_id","number":1,"Reportgeneratedat":datetime.now(),"_id":0 }},{"$out": "updatedreports"}])
6

From the docs it looks like it is only available inside update operations.

8-(

I will need to take a deeper look at this.

Try to simply use

{ "$push" : "$success" }

You will probably end up with an array of array you may then use project to only keep the appropriate element.

7

It is getting closer.

/* the original $group */

group_Result = { "$group" : {
  "_id" : "$Result" ,
  "number" : { "$push": "$number" }
} }

/* the $group by _id:null to get a single document */
group_null = { "$group" :  {
  "_id" : null ,
  "failure" : { $push : {
    "$cond" : [ { $eq : [ "$_id" , "failure" ]} , "$number" , null ]
  } } ,
  "success" : { $push : {
    "$cond" : [ { $eq : [ "$_id" , "success" ]} , "$number" , null ]
  } }
} }

Running the above 2 stages produces:

{ _id: null,
  failure: [ [ '102345890', '102345890', '102245890' ], null ],
  success: [ null, [ '100345890', '101345890' ] ] }

Then the cosmetic surgery:

filter_nulls = { "$set" : {
  "failure" : { "$filter" : { "input" : "$failure" , "cond" : { "$not" : { "$eq" : [ "$$this" , null ] } } } } ,
  "success" : { "$filter" : { "input" : "$success" , "cond" : { "$not" : { "$eq" : [ "$$this" , null ] } } } }
} }
set_0 = { "$set" : {
  "failure" : { "$arrayElemAt" : [ "$failure" , 0 ] } ,
  "success" : { "$arrayElemAt" : [ "$success" , 0 ] } 
} }

Running the pipeline

pipeline = [ group_Result , group_null , filter_nulls , set_0 ]

provides the following results:

{ _id: null,
  failure: [ '102345890', '102345890', '102245890' ],
  success: [ '100345890', '101345890' ] }
1 Like
8

Thanks @steevej ,
This solution given by you works fine now.Thanks for taking out your time and providing me the solution.
I got to learn many things from this solution :smile:

1 Like
9

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.