Please help me how to do group by based on a first matching array element status

Working with Data
,
1 
{
	"_id": "99a7692c-9687-443d-8f18-f8a28fe9ffbb",
	"version": "Version45eea229-0f5b-4977-bfa2-72bf6a9cca42",
	"first": "First18457307-c556-4231-bfc7-a1cd0ba65325",
	"last": "Laste92586b9-53bd-4cff-84d3-bdc6b33ec4ca",
	"identity": {
		"_id": "4ffa9027-6551-4c35-b8db-f60aa3bb91be@Dell.com",
		"type": "Email"
	},
	"diProfileId": "DiProfileId763b1a03-aefc-44d5-aee1-18e41564df26",
	"idp": "Idp5322c864-c39d-4bc6-9ae0-6bf0f3c79ab5",
	"type": "User",
	"links": [
		{
			"linkType": "App",
			"linkPath": "0923689a-e009-4d67-8db5-5ba40f840bf3",
			"status": "Invited",
			"inviterId": "00000000-0000-0000-0000-000000000000",
			"createdOn": "2022-06-07T12:09:58.421+00:00"
		},
		{
			"linkType": "App",
			"linkPath": "0923689a-e009-4d67-8db5-5ba40f840bf4",
			"status": "Activated",
			"inviterId": "00000000-0000-0000-0000-000000000000",
			"createdOn": "2022-06-07T12:09:58.421+00:00"
		}
	]
}

I have above document and want to group by based on first link matched status and want to get count based on filter criteria across documents. Please help on this.

2

I could write a query which works but any optimization options?

db.principals.aggregate([
  {
    $match: {
      links: {
        $elemMatch: { linkPath: /^0923689a-e009-4d67-8db5-5ba40f840bf3/s }
      }
    }
  },
  {
    $project: {
      links: {
        $filter: {
          input: "$links",
          cond: {
            $regexMatch: {
              input: "$$this.linkPath",
              regex: /^0923689a-e009-4d67-8db5-5ba40f840bf3/s
            }
          }
        }
      }
    }
  },
  {
    $project: {
      links: {
        $arrayElemAt: ["$links", 0]
      }
    }
  },
  {
    $unwind: "$links"
  },
  {
    $facet: {
      count: [{ $count: "count" }],
      group: [{ $group: { _id: "$links.status", count: { $sum: 1 } } }],
      page: [{ $sort: { "identity.id": 1 } }, { $skip: 0 }, { $limit: 1 }]
    }
  }
])
3

1 - you need an index on links.linkPath

2 - you do not need $elemMatch in your $match

3 - you do not need regular expression in your $match

The following should be sufficient.

$match : { "links.linkPath" : "0923689a-e009-4d67-8db5-5ba40f840bf3" }

4 - you do not need $regexMatch in the cond: of your $filter, simple $eq should work.

5 - you could use $reduce rather than $filter and avoid the subsequent $project and $unwind

4

Thanks Steve,

  1. Yes I will have index on links.path
  2. first is the filter to filter all the documents first which matches this criteria.
  3. I need regular expression because link path is kind of hierarchy path like a/b/c and I need to get all the records if any one has path like a, a/b or a/b/c.
  4. As I need to match kind of like path I need to use regex, would you recommend something else in this scenario.
  5. How can I use reduce as I need to consider first matching record status. Some sample would be useful.

Thanks,
Shyam.

5

Your sample document nor your description expressed that. Sorry to lead you in the wrong direction.

#5 still apply

You use $reduce more or less like your $filter. You start with the value:null, the first $regex match is your new and final value. You ignore all others. So you end up with a value equivalent to links.0 or whatever matches the regex but as an object to you do not need to $project $arrayElemAt and you do not need to $unwind.

One more thing, your page facet $sort on identity.id. But in your input document you have identity._id but your $project do not keep that fields. Try with $set stage rather than $project.