Using MongoDB $split operator on field in object nested in array in aggregation

Question

I have a MongoDB document like this:

{
"_id" : ObjectId("5c949609ff5e3d6119758730"),
"product_name" : "Shoes",
"field_to_split" : "one##two##three##four",
"assets" : [ 
    {
        "url" : "www.blabla.com/1",
        "nested_field_to_split" : "one##two##three##four"
    }, 
    {
        "url" : "www.blabla.com/2",
        "nested_field_to_split" : "one##two##three##four"
    }, 
    {
        "url" : "www.blabla.com/3",
        "nested_field_to_split" : "one##two##three##four"
    }, 
    {
        "url" : "www.blabla.com/4",
        "nested_field_to_split" : "one##two##three##four"
    }
]}

I would like to transform it into this:

{
"_id" : ObjectId("5c949609ff5e3d6119758730"),
"product_name" : "Shoes",
"field_to_split" : "one##two##three##four",
"assets" : [ 
    {
        "url" : "www.blabla.com/1",
        "nested_field_to_split" : ['one', 'two', 'three', 'four']
    }, 
    {
        "url" : "www.blabla.com/2",
        "nested_field_to_split" : ['one', 'two', 'three', 'four']
    }, 
    {
        "url" : "www.blabla.com/3",
        "nested_field_to_split" : ['one', 'two', 'three', 'four']
    }, 
    {
        "url" : "www.blabla.com/4",
        "nested_field_to_split" : ['one', 'two', 'three', 'four']
    }
]}

This has to be done during the aggregation. I was trying to to do this like this:

{  "$project":{
      "assets.nested_field_to_split":{
         "$split":[
            "$assets.nested_field_to_split",
            "##"
         ]
      }
   }
}

I have found in the documentation (https://docs.mongodb.com/manual/reference/operator/aggregation/project/) that "When nesting the fields, you cannot use dot notation inside the embedded document to specify the field, e.g. contact: { "address.country": <1 or 0 or expression> } is invalid.". So my guess it can't be done. Not like I'm trying at least. I was trying to go around it with a $map or $filter operator but with no success obviously. Please help.

Yahya · Accepted Answer · 2020-07-24 10:00:06Z

1

The following query works but might not be the fastest/neatest solution:

db.t.aggregate([
{ $unwind: "$assets" },
{ $project : { "product_name": "$product_name", "field_to_split" :"$field_to_split", "assets_url": "$assets.url", "assets_split" : { $split: ["$assets.nested_field_to_split", "##"] } } },
{ $group : { _id: { _id: "$_id", "product_name": "$product_name", "field_to_split" : "$field_to_split" }, "assets": { $push: { "url": "$assets_url", "nested_field_to_split": "$assets_split" } } } },
{ $project: { _id: "$_id._id", "product_name": "$_id.product_name", "field_to_split" : "$_id.field_to_split", "assets": "$assets" } }
])

Here's the explanation:

$unwind to bring the array out so it can be split
Using $project and $split to split the string
$group to stitch them back together
$project to fix the shape of the document in the desired output

answered Jul 24, 2020 at 10:00

Yahya

3,4743 gold badges25 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

donm Over a year ago

Works great, exactly what I need, thank you, but I can't believe it can't be done as I tried in my question. It seems counterproductive to add 4 extra steps to aggregation like that.

Collectives™ on Stack Overflow

Using MongoDB $split operator on field in object nested in array in aggregation

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related