$ (dollar sign) project query with arrays

Working with Data
1

Hi,
I have the following data:

{
_id: "1"
transitions: [
    {
       "_id" : "11"
      "name" : "Tr1"
      "checkLists" : [
                           { _id: "111", name: "N1"},
                           { _id: "112", name: "N2"}
                           ]
     }  
  ]
}

I used the following code to get the name N2 by query of _id:112

db.collection.findOne({ 'transitions.checkLists._id: new ObjectId("112") } }}, {  'transitions.checkLists.$': 1 })

but the result returns back both of them:

{ _id: ObjectId("1"),
  transitions: 
   [ { checkLists: 
        [ { name: 'N1', _id: ObjectId("111") },
          { name: 'N2', _id: ObjectId("112") } ] } ] }

I would like to find and get only the name N2 by query of _id:112
Expected Result:

{ _id: ObjectId("1"),
  transitions: 
   [ { checkLists: 
        [ { name: 'N2', _id: ObjectId("112") } ] } ] }
2

Hi Mehran, I’m not sure if it’s an “elegant” solution or not very scalable, but I got to this query

db.collection.aggregate(
  [
    {
      $match: {
        "transitions.checkLists._id": "112",
      },
    },
    {
      $unwind: "$transitions",
    },
    {
      $unwind: "$transitions.checkLists",
    },
    {
      $match: {
        "transitions.checkLists._id": "112",
      },
    },
  ]
)
1 Like
3

Or maybe this

db.collection.aggregate(
    [
        {
            $match: {
            "transitions.checkLists._id": "112",
            },
        },
        {
            $unwind: "$transitions",
        },
        {
            $project: {
            transitions: {
                $filter: {
                input: "$transitions.checkLists",
                as: "transition",
                cond: {
                    $eq: ["$$transition._id", "112"],
                },
                },
            },
            },
        },
    ]
)

[quote=“Mehran_Ishanian1, post:1, topic:201482, full:true”]
Hi,
I have the following data:

{
_id: "1"
transitions: [
    {
       "_id" : "11"
      "name" : "Tr1"
      "checkLists" : [
                           { _id: "111", name: "N1"},
                           { _id: "112", name: "N2"}
                           ]
     }  
  ]
}

I used the following code to get the name N2 by query of _id:112

db.collection.findOne({ 'transitions.checkLists._id: new ObjectId("112") } }}, {  'transitions.checkLists.$': 1 })

but the result returns back both of them:

{ _id: ObjectId("1"),
  transitions: 
   [ { checkLists: 
        [ { name: 'N1', _id: ObjectId("111") },
          { name: 'N2', _id: ObjectId("112") } ] } ] }

I would like to find and get only the name N2 by query of _id:112
Expected Result:

{ _id: ObjectId("1"),
  transitions: 
   [ { checkLists: 
        [ { name: 'N2', _id: ObjectId("112") } ] } ] }

@Paulo_Cesar_Benjamin_Junior1

1 Like
4

Hello @Mehran_Ishanian1,

The projection positional $, the condition will work only in the first level of the array,

You can use aggregation operators $map and $filter operators to filter the nested array,

  • $map to iterate loop of transitions array
  • $filter to iterate loop of checkLists array and filter the list by _id
  • $mergeObjects to merge current object of transitions and filtered result of checkLists
db.collection.findOne(
  { "transitions.checkLists._id": new ObjectId("112") },
  {
    "transitions": {
      "$map": {
        "input": "$transitions",
        "in": {
          "$mergeObjects": [
            "$$this",
            {
              "checkLists": {
                "$filter": {
                  "input": "$$this.checkLists",
                  "cond": { "$eq": ["$$this._id", new ObjectId("112")] }
                }
              }
            }
          ]
        }
      }
    }
  }
)

Playground

1 Like
5

Thanks for your solution

1 Like
6

Thank you for the solution

7

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.