MongoDB match document where embeded document (array of objects) has (nested) array field occuring value only once

Question

I have the following schema in my database:

{
   _id: Number,
   reagents: [ //Array of objects (embedded) FIND IF certain tag in v_class occurred only once in array
       {
           _id: Number,
           v_class: Array, //A single array (string only): ['TEST','PREMIUM']
       },
       {
           _id: Number,
           v_class: Array, //A single array (string only): ['TEST','PREMIUM']
       },
       {
           _id: Number,
           v_class: Array, //A single array (string only): ['TEST','PREMIUM']
       }
   ],
   other_fields: other_values,
}

MongoPlayground

The question is all about:

How to find only that documents, where reagents field have only one item (reagent, embedded documents), among v_class array field consist the word PREMIUM.

So from the selected collection above, I want to have only document with _id: 2 because it's the only document where reagents field have THE ONLY document, where in field called v_class word PREMIUM occurring only once.

UPD: I have achieved a bit simple results after $group stage, MongoPlayground (after group). So as for now, I still need to find docs where v_class array field occurring PREMIUM value exactly once in it's array`

UPD2: I have new version has appeared. MongoPlayground. This query is fine, but it shows all the PREMIUM except that occurred just once.

In your example, there is no document with a sigle PREMIUM in the v_class array of reagents. — Mickael B.
– Mickael B., Commented May 5, 2020 at 17:38
Or do you want to find documents that have "premium" occurring exactly once in any of the v_classes (not is the only element of a v_class)? — D. SM
– D. SM, Commented May 5, 2020 at 17:43
@MickaelB. single doesn't mean that v_class array field will have only ["PREMIUM"] value, it means that I need to find only one doc, with v_class = consist PREMIUM so it could be like `['X', 'PREMIUM'] — AlexZeDim
– AlexZeDim, Commented May 5, 2020 at 17:44
@Oleg, y you right, thank you so much for pointing me out, I will correct the description. — AlexZeDim
– AlexZeDim, Commented May 5, 2020 at 17:45

D. SM · Accepted Answer · 2020-05-05 18:21:07Z

1

$sum needs a numeric value to make sense.

Once you have the sum, an additional $match stage can compare the sum to 1.

https://mongoplayground.net/p/TKQowD-vd0f

answered May 5, 2020 at 18:21

D. SM

14.6k3 gold badges15 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

AlexZeDim Over a year ago

I just found it: mongoplayground.net/p/JWWy8LPgk5j So the key question for now, how can I return the original doc by _id cause recipe_id field represents it in my case?

D. SM Over a year ago

I imagine performing another $lookup to join the original collection to the existing results will achieve that.

AlexZeDim Over a year ago

$out ? (docs.mongodb.com/manual/reference/operator/aggregation/out)

D. SM Over a year ago

What is your question?

AlexZeDim Over a year ago

There is no question there, actually you are right about $lookup stage cause at first I think that $out could return the original doc from the collection, but instead it creates a new collection of documents with a specific name.

AlexZeDim · Accepted Answer · 2020-05-05 23:44:52Z

I have found my own way to find the result, separately from Oleg, but he was first, so I upvote him and accept as answer his variant.

My query has the only one difference. It returns the original documents itself, not just reference fields.

Full query:

db.collection.aggregate([
  {
    $match: {
      reagents: {
        $elemMatch: {
          _id: 12
        }
      }
    }
  },
  {
    $group: {
      _id: {
        v_class: "$reagents.v_class",
        recipe_id: "$_id"
      },
      data: {
        $push: "$$ROOT"
      }
    }
  },
  {
    $unwind: "$_id.v_class"
  },
  {
    $match: {
      "_id.v_class": "PREMIUM"
    }
  },
  {
    $group: {
      _id: "$_id.recipe_id",
      count: {
        $sum: 1,

      },
      data: {
        "$first": "$data"
      }
    }
  },
  {
    $match: {
      count: 1
    }
  }
])

It finds the exact item in reagents (Array) field by ID, and it's document has a v_class (Array) field check if it's value/tag occurred only once.

MongoPlayground

Collectives™ on Stack Overflow

MongoDB match document where embeded document (array of objects) has (nested) array field occuring value only once

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related