At time of writing, what you are asking for is not possible to do with SOQL queries alone
(and also not in a single query).
UserOrGroupId is an example of a "polymorphic" relationship field. While normal relationship fields can only point to on type of SObject, polymorphic relationships can hold an Id for one of many potential SObject types (hence the "poly" bit).
If it were possible
Salesforce has given us the ability to work with polymorphic relationships using things like TYPEOF and .Type. The relevant documentation on working with polymorphic relationships is here.
If this were possible, your query would end up looking something like this
SELECT
GroupId,
TYPEOF UserOrGroup
WHEN User THEN Id, Name, IsActive
END
FROM GroupMember
WHERE UserOrGroup.Type = 'User' AND UserOrGroup.IsActive = true
All of the things in that query are supported by that documentation I linked; except for the UserOrGroup.IsActive = true bit.
A quick test that I ran using the Event SObject suggests that the syntax would be valid (though it may not be useful). The following query works:
SELECT Id,
TYPEOF What
WHEN Account THEN Phone
WHEN Opportunity THEN Amount
END
FROM Event
WHERE What.Type IN ('Account', 'Opportunity') AND What.Phone = null
The "may not be useful" part is because it'll return rows for Opportunities even though Opportunity has no standard "Phone" field. Filtering on a particular value for Phone does not appear to work (no syntax error, but it doesn't return the correct results)
In the case of GroupMember
Salesforce complains that UserOrGroup is not a valid field.
When you're working with related data in a query, we drop the "Id" bit from standard relationship fields (so UserOrGroupId becomes UserOrGroup), and custom relationship fields change the __c to __r (e.g. MyOpportunityLookup__c => MyOpportunityLookup__r)
So because Salesforce can't properly recognize its own relationship field, we can't use any of the features normally available to work with relationships.
Welcome to Salesforce Shenanigans
There are a handful of Salesforce features that they seemingly plucked from other companies (that they ostensibly acquired) and just slapped into Salesforce and hold it on with duct tape.
These features aren't nearly integrated as well as things they've developed themselves, and it shows. Sometimes, it'll just have a completely different "feel" to it (looking at you, CRM Content Libraries). Other times, like this case, the "standard" behavior and tools don't apply.
So in this case, to get exactly what you want, you will need:
- To run one query on GroupMember
- To run a second query, on User
- Use Apex to stitch the results together (store the User query results in a Map so you can get at the data for the User Id you'll get in your query on GroupMember)
