Grouping by 2 attributes - an array and a string field

SQL++
,
1

Hi! I am trying to get summary information from different type of documents that I have stored in a DB. I have 2 type of documents: 1 to store users and 1 to store role information.
Users reference the roles by the role document id.

Users:

doc-id: user1
{
    "pic": "picture1.jpg",
    "origin" : "INTERNAL",
    "roles" : ["role1", "role2"],
    "type": "user-doc"
}
doc-id: user2
{
    "pic": "picture2.jpg",
    "origin" : "EXTERNAL",
    "roles" : ["role1"],
    "type": "user-doc"   
}

Role Document

doc-id: "role1"
{
    "name": "Role One",
    "type": "role-doc"
}
doc-id: "role2"
{
    "name": "Role Two",
    "type": "role-doc"
}

I need to generate an output similar to the following structure:

[{
    "Role One" : {
      "INTERNAL" : 1,
      "EXTERNAL" : 1        
    },
    "Role Two" : {
      "INTERNAL" : 1,
      "EXTERNAL" : 0
    }
}]

I have been trying different queries and I am only able to get the aggregation by roles but I am not sure how to nest the users based on the origin field as output.

SELECT roleDoc.name, COUNT(1) AS count, META(roleDoc).id
FROM `myBucket` u
UNNEST u.roles AS userRoles
    JOIN `myBucket` r ON userRoles = META(r).id
WHERE u.type = "user-doc"
    AND r.type = "role-doc"
GROUP BY r AS roleDoc

Which gives me an output like:

[
  {
    "count": 2,
    "id": "role1",
    "name": "Role One"
  },
  {
    "count": 1,
    "id": "role2",
    "name": "Role Two"
  }
]

Does anybody have some recommendations on how to approach this query?
Thanks!

2 
SELECT RAW OBJECT v.name:{v.EXTERNAL, v.INTERNAL}
           FOR v IN (SELECT r.name, d.EXTERNAL, d.INTERNAL
                     FROM (SELECT ur,
                               SUM(CASE WHEN u.origin == "EXTERNAL" THEN 1 ELSE 0 END) AS EXTERNAL,
                               SUM(CASE WHEN u.origin == "INTERNAL" THEN 1 ELSE 0 END) AS INTERNAL
                           FROM `myBucket` AS u
                           UNNEST u.roles AS ur
                           WHERE u.type = "user-doc"
                           GROUP BY ur) AS d
                     JOIN `myBucket` AS r ON KEYS d.ur
                     WHERE r.type = "role-doc")
            END;
1 Like