Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

SOLVED

SQL2 query to list all users in a group

Avatar

Level 2

How to get the list of all users in a group using SQL2 query?

I tried this:

select * from [rep:User] as user inner join [rep:Group] as group on user.[jcr:uuid] = group.[rep:members] where group.[rep:principalName]="myGroup"

 

 

1 Accepted Solution

Avatar

Correct answer by
Level 10
4 Replies

Avatar

Community Advisor

Hi,

You can do it this without a query.

 

Session session = getSubserviceSession();
JackrabbitSession jcrSession = (JackrabbitSession) session;
UserManager uM = jcrSession.getUserManager();
Group group = (Group) uM.getAuthorizable("my-group");
terator<Authorizable> itr = group.getMembers();
while (itr.hasNext()) {
  Object obj = itr.next();
  if (obj instanceof User) {
    User user = (User) obj;
    String uid = user.getID();
  }
}

Avatar

Correct answer by
Level 10

@KhalidMomin1  I recommend to try the "Users to CSV Exporter" of ACS AEM Commons instead

 

https://adobe-consulting-services.github.io/acs-aem-commons/features/exporters/users/index.html

 

Avatar

Community Advisor

Hi @KhalidMomin1 
Getting group members via SQL2 query won't be possible because it doesn't store the actual path of members. Instead it does store as WeakReference[]. 

Screenshot 2023-01-10 at 14.56.47.png

As you can see, property holds the members references and not actual path and there's no way using SQL2 query to basically iterate and get each reference absolute path even using inner join IMO. So, the possible way is what described by @arunpatidar 

Best regards,
Himanshu Singhal