Jira SQL for Nested Groups

[Nikhil Karkare]

I am trying to create a report that looks like following (by avoiding duplicates):

Parent_Group | child_group | member

The only relevant table I have found so far is cwd_membership. I found that one child_group belongs to multiple parent groups. Below is one example:

 

If I use the below SQL, I get the correct list of members who belong to child group. I haven't been able to find a way to get the details about what parent group they belong to. For example, if user "John" is a part of Project Management group, how will I know if John is the member of Project Management subgroup that belongs to one of the above parent groups? 

Select usr.parent_id, usr.child_id, grp.parent_name AS parent_group, usr.parent_name AS child_group, usr.child_name AS user_name
from 
(Select parent_id, child_id, membership_type, parent_name, child_name
from public.cwd_membership WHERE membership_type = 'GROUP_GROUP') AS grp
JOIN
(Select parent_id, child_id, membership_type, parent_name, child_name
from public.cwd_membership WHERE membership_type = 'GROUP_USER') AS usr
ON usr.parent_id = grp.child_id