How to group documents by field1 and then query to see field2 is missing a value?

Working with Data
,
1

I have a collection that holds “status” for “transaction_id” and I want to find out which transaction_ids are missing certain statuses. Statuses can be - “sent”, “delivered”, “undelivered”, “failed”, “queued”. Most transaction_ids have multiple statuses so there are multiple documents with the same transaction_id but the status value is different. I want to find out transaction_ids that have these status values missing - “delivered” & “undelivered” & “failed”. How do I do this using a query? “transaction_id” is indexed and I can narrow down the documents using another indexed field “created_by” date.

2

Hello @Ram_Mulay ,

Welcome to The MongoDB Community Forums! :wave:

To understand your use case better, please provide more details, such as:

  • MongoDB Version being used
  • Your requirements of this scenario
  • Collections being used
  • Some sample documents with respect to the collections
  • Expected output document/s
  • Any queries that you worked on to achieve the required results?

Regards,
Tarun

4

MongoDB version - 4.4.16

Requirements - I have given sample documents below. As you can see, they have different statuses. The collection has many documents with many transaction ids. I want a query that gives me transaction ids with missing statuses. - “delivered” & “undelivered” & “failed”. So I need to get transaction_id=2 as a result of this query since none of its statuses are “delivered” or “undelivered” or “failed”.

Sample documents in the collection -
{“_id”: “1”, “status”: “sent”, “transaction_id”: “1”}
{“_id”: “2”, “status”: “queued”, “transaction_id”: “1”}
{“_id”: “3”, “status”: “undelivered”, “transaction_id”: “1”}
{“_id”: “4”, “status”: “sent”, “transaction_id”: “2”}
{“_id”: “5”, “status”: “queued”, “transaction_id”: “2”}

what I have tried - $ne or $nin do not work because there are other documents with the same transaction_id which have statuses that are not “delivered”, “undelivered”, “failed”. Hence the query returns both transaction_ids - “1” and “2”.

Maybe what I need is a map-reduce function. First get all statues tied to a transaction_id and then have a reduce function to check if the list of statuses are missing delivered", “undelivered”, “failed”. If they are, then choose that document. However, I could not find examples of map-reduce to do this type of query. Also, the mongoDB documentation says map-reduce is deprecated and wants us to use aggregations. I cannot figure out how to do this using an aggregation.

5

Sample documents for all you use-cases including:

Read Formatting code and log snippets in posts before supplying all documents we need to experiment.

Share exactly the code you

My approach would be to group on transaction_id using $addToSet for the status. Then a $match a $nin on the $addToSet array.

1 Like
6

That worked, thanks @steevej ! Here is the query for anyone else who might run into this.

db.getCollection('statuses').aggregate ([
{
  $group:
    {
      _id: { tran_id: "$transaction_id" },
      statuses: { $addToSet: "$status" }
    }
},
{
  $match: { statuses: { $nin: ["failed", "delivered", "undelivered"] } }
}
])
1 Like
7

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