How to write a custom function to split a document into multiple documents of same Id

Question

I am trying to split a document which has the following fields of string type:

{
 "_id" : "17121",
 "firstName": "Jello",
 "lastName" : "New",
 "bio" :"He is a nice person."
}

I want to split the above document into three new documents For Example:

{
"_id": "17121-1",
"firstName": "Jello"
}
{
"_id": "17121-2",
"firstName": "New"
}
{
"_id": "17121-3",
"bio": "He is a nice person."
}

Can anyone suggest how to proceed?

db.coll1.find().forEach(function(obj){
   // I want to extract every single field. How to iterate on the field within this Bson object(obj) to collect every field.?
});

or any suggestion to do with aggregation pipeline in MongoDB.

AlexDenisov · Answer 1 · 2017-09-29T12:19:53.293

Anu. Here are two options you can use.

The first option is pretty straightforward, but it requires you to hardcode _id' indexes yourself.

db.users.aggregate([
    {
        $project: {
            pairs : [
                { firstName: '$firstName', _id : { $concat : [ { $substr : [ '$_id', 0, 50 ] }, '-1' ] } },
                { lastName: '$lastName', _id : { $concat : [ '$_id', '-2' ] } },
                { bio: '$bio', _id : { $concat : [ { $substr : [ '$_id', 0, 50 ] }, '-3' ] } }
            ]
        }
    },
    {
        $unwind : '$pairs'
    },
    {
        $replaceRoot: { newRoot: '$pairs' }
    }
])

The second option does a little bit more job and is somewhat more tricky. But it is probably easier to extend if you ever need to add another field.

db.users.aggregate([
    {
        $project: {
            pairs : [
                { firstName: '$firstName' },
                { lastName: '$lastName' },
                { bio: '$bio' }
            ]
        }
    },
    {
        $addFields: {
            pairsReference : '$pairs'
        }
    },
    { 
        $unwind: '$pairs'
    },
    {
        $addFields: {
            'pairs._id' : { $concat: [ { $substr : [ '$_id', 0, 50 ] }, '-', { $substr: [ { $indexOfArray : [ '$pairsReference', '$pairs' ] }, 0, 2 ] } ] }
        }
    },
    {
        $replaceRoot: { newRoot: '$pairs' }
    }
])

You can redirect results of both queries into another collection by using $out stage.

UPD:

The only reason you get the error is that one of the _ids is not a string.

Replace the first parameter of $concat ($_id) with the following expression:

{ $substr : [ '$_id', 0, 50 ] }

Thanks, in the both approaches, it pops an "errmsg" : "$concat only supports strings, not int". Any suggestion? — Anu, Sep 28 '17 at 17:52
Hm, which version of MongoDB you use? I'm on MongoDB 3.4.1, everything works so far. — AlexDenisov, Sep 28 '17 at 19:21
Anu, I updated the answer. It must be because of one or more of ids is not a string. — AlexDenisov, Sep 29 '17 at 12:14

s7vr · Accepted Answer · 2017-09-29T01:10:36.643

1

You can use the below aggregation query.

The below query will convert each document fields into key value document array followed by $unwind while keeping the index and $replaceRoot with merge to produce the desired output.

$objectToArray to produce array (keyvalarr) with key (name of the array field)-value (array field) pair.

$match to remove the _id key value document.

$arrayToObject to produce the named key value while adding new _id key value pair and flatten array key values.

db.coll.aggregate([
  {
    "$project": {
      "keyvalarr": {
        "$objectToArray": "$$ROOT"
      }
    }
  },
  {
    "$unwind": {
      "path": "$keyvalarr",
      "includeArrayIndex": "index"
    }
  },
  {
    "$match": {
      "keyvalarr.k": {
        "$ne": "_id"
      }
    }
  },
  {
    "$replaceRoot": {
      "newRoot": {
        "$arrayToObject": [
          {
            "k": "_id",
            "v": {
              "$concat": [
                {
                  "$substr": [
                    "$_id",
                    0,
                    -1
                  ]
                },
                "-",
                {
                  "$substr": [
                    "$index",
                    0,
                    -1
                  ]
                }
              ]
            }
          },
          "$keyvalarr"
        ]
      }
    }
  }
])

edited Sep 29 '17 at 01:10

answered Sep 28 '17 at 14:54

s7vr

73,656
11
106
127

Thanks I got an "errmsg" : "unrecognized option to $replaceRoot stage: $newRoot, only valid option is 'newRoot'.". could you please provide a suggestion on it? – Anu Sep 28 '17 at 18:31
Sorry typo in query. Remove the `$` from `$newRoot. Updated answer. – s7vr Sep 28 '17 at 18:32
thankyou @Veeram, Now I am getting an "errmsg" : "$concat only supports strings, not int". When I searched for the msg [https://stackoverflow.com/questions/37470172/convert-objectid-to-string-in-mongo-aggregation], the other post suggested to use a $substring, which is present in the above query, but I think it has a problem in $concat the "$_id" field which is Integer. So, I bind the whole object within $concat with $substr and got a new error "errmsg" : "$substrBytes: starting index must be a numeric type (is BSON type string)". Any suggestion? – Anu Sep 28 '17 at 19:01
Np. You can wrap the numeric `$_id` in `$substr`. Something like `"v": { "$concat": [ { "$substr": [ "$_id", 0, 1 ] }, "-", { "$substr": [ "$index", 0, 1 ] } ] }` – s7vr Sep 28 '17 at 19:07
a follow-up question, even though you have used $concatArray to concat array of objects, it is giving "errmsg" : "$concatArrays only supports arrays, not object". – Anu Sep 28 '17 at 20:08
Refactored and tested. Please verify. – s7vr Sep 29 '17 at 01:14

How to write a custom function to split a document into multiple documents of same Id

2 Answers2