How can I complete a string concatenation inside a project stage using C# and the Mongo aggregation pipeline?

Question

I'm trying to perform a string concatenation within a project stage of a MongoDb aggregation pipeline operation.

I need to add a field called "coid" to a document, which is to be the result of a string concation between 2 strings:

the string literal: "prefix-"
the string coming from the "SecId" field in the first document of the "values" array field.

My attempt is below, but it keeps generating a compiler error. Does anyone know how I can accomplish this string concatenation, within the aggregation pipeline project stage?

    new BsonDocument("$project", 
                    new BsonDocument
                    {
                        { "_id", 1 },
                        { "coid",
                        new BsonDocument("$concat",new BsonDocument[
                              new BsonDocument("prefix-"),
                              new BsonDocument("$first", "$values.SecId")])
                        }
                    })

Edit: Here is an example of one string concatenation: If the value of $values.Secid is "12345", then the concatenation should be "prefix-12345".

Update here is an enlarged view of my pipeline

            new BsonDocument("$lookup",
                new BsonDocument
                {
                    {"from","accounts"},
                    { "localField", "ig" },
                    { "foreignField", "ipi" },
                    { "as", "accounts" },
                }),
            new BsonDocument("$project",
                new BsonDocument
                {
                    { "_id", 1 },
                    {
                        "coid",
                        new BsonDocument("$first", "$accounts._id")
                    },
                    { "newField",
                        new BsonDocument("$concat","prefix-" + [from first element of $accounts array, take the _id value]
                    },
                }),
           new BsonDocument("$out", LocalOutputCollection)

score 1 · Answer 1 · answered May 11 '21 at 01:33

1

There are a couple problems with that code:

The literal passed in the $concat array should be string, not BsonDocument
The $first operator is only available in a group stage, you probably need to use $arrayElemAt

new BsonDocument("$concat",
     new BsonDocument[
                      "prefix-",
                      new BsonDocument(
                            "$arrayElemAt", 
                            new BsonDocument[ "$values.SecId", 0]
                      )
     ]
)

answered May 11 '21 at 01:33

Joe

25,000
3
22
44

with this code, I have a few problems. Under "$concat" and "$arrayElemAt" I have the error: (cannot convert from string to Dictionary) Under "prefix-" and "$values.SecId" I have the error: (cannot covert from string to int). I am using Mongo driver 2.11.5 – DalhousieDuck May 11 '21 at 13:41

Dĵ ΝιΓΞΗΛψΚ · Answer 2 · 2021-05-11T05:11:02.520

1

you can easily do $concat with the AsQueryable interface like so:

var result = collection
    .AsQueryable()
    .Select(x => new
    {
        x.Id,
        coid = "prefix-" + x.Values.First().SecId
    })
    .ToList();

it generates the following projection:

{
    "$project": {
        "Id": "$_id",
        "coid": { "$concat": ["prefix-", { "$arrayElemAt": ["$Values.SecId", 0] } ]
        }
    }
}

edited May 11 '21 at 05:11

answered May 11 '21 at 05:05

Dĵ ΝιΓΞΗΛψΚ

5,068
3
13
26

Thanks- how are you able to see the resulting javascript that is produced? Are you able to set a breakpoint somewhere and find it in a variable? – DalhousieDuck May 11 '21 at 13:34
if i remember correctly, you can simply do `ToString()` instead of `ToList()` on the `AsQueryable` interface. but i prefer to use [database profiling](https://docs.mongodb.com/manual/tutorial/manage-the-database-profiler/). with that you can see all queries received by the database. basically you first enable profiling wit h `db.setProfilingLevel(2)` and then to see the last 10 queries you do `db.system.profile.find().limit(10).sort({ts:-1}).pretty()` – Dĵ ΝιΓΞΗΛψΚ May 11 '21 at 14:29
Thanks that makes sense. Just one other issue I see with your solution is that I don't think I can integrate it into my pipeline operation- I believe I will have to query out all documents from the output collection (after my pipeline finishes), and then run your projection against it right? Isn't there a way to append a $concat stage to my pipeline that can do this? – DalhousieDuck May 11 '21 at 14:33
@DalhousieDuck what does your pipeline look like? can you add it to the question so i can have a look pls. – Dĵ ΝιΓΞΗΛψΚ May 11 '21 at 14:36
I've updated my question to show my pipeline. I do a $lookup, followed by a $project, followed by an $out. I need to perform the concatenation inside the $project stage, in the "newField" field. I need to join the string literal "prefix-" to the value of the _id field from the $first document in the array-field called "accounts" – DalhousieDuck May 11 '21 at 15:35

How can I complete a string concatenation inside a project stage using C# and the Mongo aggregation pipeline?

2 Answers2