I have this sample data:

[
  {
    "id": 1,
    "marcadores": [
      { "k": "G", "v": "00" },
      { "k": "1", "v": "A" },  
    ]
  },
  {
    "id": 2,
    "marcadores": [
      { "k": "1", "v": "A" },
    ]
  },
  {
    "id": 3,
    "marcadores": [
      { "k": "G", "v": "03" },
      { "k": "P", "v": "55" }
    ]
  }
]

I would like to filter those documents with this critera:

• marcadores.k: "G" and marcadores.v: { $ne: "00" } ($elemMatch). In the example, "id": 3 meets this criteria
• OR
• document has no marcadores.k: "G". In the example, "id": 2 meets this criteria.

Expected output:

[
  {
    "id": 2,
    "marcadores": [
      { "k": "1", "v": "A" },
    ]
  },
  {
    "id": 3,
    "marcadores": [
      { "k": "G", "v": "03" },
      { "k": "P", "v": "55" }
    ]
  }
]

What's the best/cleanest way to solve this query? It seems easy, but I am getting some troubles to get the right data.

You can use this playground

Thanks in advanced

CodePudding user response：

Using find we can use $not with $elemMatch:

db.collection.find(
  {marcadores: {$not: {$elemMatch: {"k": "G", "v": "00"}}}}
)

See how it works on the playground example - find

But if you need it inside an aggregation pipeline, you can use $filter

db.collection.aggregate([
  {$set: {
      res: {$filter: {
          input: "$marcadores",
          cond: {$and: [{$eq: ["$$this.v", "00"]}, {$eq: ["$$this.k", "G"]}]}
      }}
  }},
  {$match: {"res.0": {$exists: false}}},
  {$unset: "res"}
])

See how it works on the playground example - aggregation