CODEWITHSUNDEEP
mongodbmongodb-querynosqlaggregation-framework
pliniocf
pliniocf

Reputation: 374

MongoDB query: aggregate with "findOne"

I'm trying to make a query on MongoDB 3.4 where I add a field for one specific element of an array. Example of the object:

    {
        "_id": 1,
        "people": [
            {
                "name": "Jhon Smith",
                "age": "30",
                "state": "NY"
            },{
                "name": "Clint Mercer",
                "age": "50",
                "state": "NY"
            },{
                "name": "Walter Smith",
                "age": "40",
                "state": "WI"
            }
        ]
    }

And I want to make a query where I'll add to this document an attribute with the first person with "Smith" in it's name. Example:

    {
        "_id": 1,
        "people": [
            {
                "name": "Jhon Smith",
                "age": "30",
                "state": "NY"
            },{
                "name": "Clint Mercer",
                "age": "50",
                "state": "NY"
            },{
                "name": "Walter Smith",
                "age": "40",
                "state": "WI"
            }
        ],
        "firstSmith": {
            "name": "Jhon Smith",
            "age": "30",
            "state": "NY"
        }
    }

I already have the _id of the document I want, but I can't understand how to make a query like this. I'm trying using aggregate with "$match" for the id and "$addFields" after, but I can't make a query that works for this field to find exactly what I want. I think it would be similar to the "findOne" query, but I can't find anything that works on "$addFields".

Obs: I DON'T want the "firstSmith" to be an array with just one "people" inside, I want it as is in the example.

I'd appreciate some help with this one.

Upvotes: 2

Views: 242

Answers (1)

NeNaD
NeNaD

Reputation: 20354

  • $match - to filter the relevant document
  • $filter with $regexMatch - to filter people array by the name property
  • arrayElemAt - to get only the first element of above array
  • $addFields - to add new field with value from above result
db.collection.aggregate([
  {
    "$match": {
      "_id": 1
    }
  },
  {
    "$addFields": {
      "firstSmith": {
        "$arrayElemAt": [
          {
            "$filter": {
              "input": "$people",
              "cond": {
                "$regexMatch": {
                  "input": "$$this.name",
                  "regex": "Smith"
                }
              }
            }
          },
          0
        ]
      }
    }
  }
])

Working example

Upvotes: 1

Related Questions