Mongo project add to array

Question

I'm trying to add an item to array in aggregation, but seem to be stuck with it for a couple of days. Here is what it looks like:

db={
  Students: [
    {
      "id": "123",
      "title": "John",
      "marks_in_subjects": [
        {
          "marks": 90,
          "subject_id": "abc"
        },
        {
          "marks": 92,
          "subject_id": "def"
        }
      ]
    }
  ],
  Subjects: [
    {
      "id": "abc",
      "name": "math"
    },
    {
      "id": "def",
      "name": "physics"
    }
  ]
}

And I do the project and concat title to new field like this:

db.Students.aggregate([
  {
    $unwind: "$marks_in_subjects"
  },
  {
    "$lookup": {
      "from": "Subjects",
      "localField": "marks_in_subjects.subject_id",
      "foreignField": "id",
      "as": "subjects"
    }
  },
  {
    $unwind: "$subjects"
  },
  {
    $project: {
      _id: "$_id",
      title: "$title",
      titleAll: {
        $concat: [
          "$title",
          " > ",
          "$subjects.name",
          
        ]
      },
      
    }
  },
  
])

And this is the output that I get:

[
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "title": "John",
    "titleAll": "John \u003e math"
  },
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "title": "John",
    "titleAll": "John \u003e physics"
  }
]

Now, I would like my output to have first item in array with just the real title, and then listing all of this, like this:

[
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "title": "John",
    "titleAll": "John"
  },
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "title": "John",
    "titleAll": "John \u003e math"
  },
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "title": "John",
    "titleAll": "John \u003e physics"
  }
]

Is there any way to achieve it? I tried using $cond, but does not seem to work. Here is the link to Mongo playground

Answer 1

You can do it in a simpler way:

No need to $unwind and $group again. The missing step was to concat the title to the titleAll array.

db.Students.aggregate([
  {
    $lookup: {
      from: "Subjects",
      localField: "marks_in_subjects.subject_id",
      foreignField: "id",
      as: "subjects"
    }
  },
  {
    $project: {subjects: "$subjects.name", title: 1}
  },
  {
    $addFields: {
      titleAll: {
        $map: {
          input: "$subjects",
          as: "item",
          in: {$concat: ["$title", " > ", "$$item"]}
        }
      }
    }
  },
  {
    $project: {
      title: "$title",
      titleAll: {"$concatArrays": ["$titleAll", ["$title"]]}
    }
  },
  {
    $unwind: "$titleAll"
  }
])

Sample MongoDB Playground

Answer 2

"I only want to see 1 users rows." Do you mean: I only want to see rows from one specific user?

Fetch no more data than your caller wants

First of all, when fetching data from a database, don't fetch all items. This would be a waste of processing power if the caller only wants the FirstOrDefault, or only the first few items.

Furthermore: whenever an object implements IDisposable, use using, so you can be certain that the object is always Disposed

public static IEnumerable<actionsVAL> FetchActions()
{
    using (OleDbConnection dbConnection = new OleDbConnection(...))
    {
        const string sqlText = "SELECT  * from actions";
        using (OleDbCommand cmdactionlist = new OleDbCommand(sqlText, dbConnection.conn))
        {
            dbConnection.Open();
            using (OleDbDataReader dataReader = cmdactionlist.ExecuteReader())
            while (dataReader.Read())
            {
                actionsVal action = new actionsVAL()
                {
                   Actionid = int.Parse(dr["actionid"].ToString());
                   Actionuserid = int.Parse(dr["userid"].ToString());
                   Actionbookid = int.Parse(dr["bookid"].ToString());
                   ...
                };
                yield return action;
            }
        }
    }
}

I also added opening and closing the OleDbConnection. It is not wise to keep this connection open for a lengthy time. If you want, you can remove this here. I'm not sure if you want that the caller creates a dbConnection for you, that you decide to change it (= Open it). If you trust your caller to create a dbConnection, then trust him also that he opens it for you.

All the using statements, make sure that the created objects are properly Closed and Disposed, even after Exceptions.

The yield return, makes sure that the objects are not disposed until the caller has stopped enumerating. Also: if the caller only enumerates the first three objects, the rest is not enumerated (while dataReader.Read()).

Show only the data of user X

Ok, so now you have a method to fetch all actionsVal, one by one. You only want to show the actionsVal of the user with a value of property ActionUserId equal to the Id of user X

The best method would be to create a FetchActions(User X):

IEnumerable<ActionsVal> FetchActions(User X)
{
    const string sqlText = "Select * from actions where actionUserId = ..."

Well you do know SQL better that I.

If you don't want to create a special procedure, you can use FetchActions():

IEnumerable<ActionsVal> FetchActions(User x)
{
    int actionUserId = x.ActionUserId;
    return this.FetchActions()
        .Where(action => actionUserId == actionUserId);
}

The best way to display these values is to use the DataSource of the DataGridView.

In the constructor of your form you have defined which column will show which property of ActionVal:

InitializeComponents();

columnId.DataPropertyName = nameof(ActionsVal.Id);
columnName.DatePropertyName = nameof(ActionsVal.Name);
...

To show the fetched ActionVals:

public BindingList<ActionsVal> DisplayedActionVals
{
    get => (BindingList<ActionsVal>)this.dataGridView1.DataSource;
    set => this.dataGridView1.DataSource = value;
}

So to show only the ActionsVal of User X:

void ShowActionsVal(User x)
{
    IEnumerable<ActionsVal> actionValsToDisplay = this.FetchActions(x);
    this.DisplayedActionsVals = new BindingList<ActionsVal)(actionsValsToDisplay.ToList());
}

That's all, all ActionsVals of user X are displayed. If allowed, the operator can add / remove rows and edit cell. He can even rearrange columns and rows. After a while he indicates he has finished editing by clicking a button:

void OnButtonOk_Clicked(object sender, ...)
{
    this.ProcessEditedActions();
}

void ProcessEditedActions()
{
    ICollection<ActionsVal> editedActions = this.DisplayedActionsVals;

    // find out which ActionsVals are added / removed / changed
    this.ProcessEditedActions(editedActions);
}