Nested array projection

SQL++
1

Hi,

I have a document like the one below.

{
"id": "23234sdsdf-23234sds-2343sdfsdf",
"type": "organization",
"name": "org1",
"roles": [
    {
        "name": "org:admin",
        "users": [
            {
                "id": "user1@email.com"
            },
            {
                "id": "user2@email.com"
            }
        ],
        "permissions": [
            "create",
            "delete"
        ]
    }
],
"workspaces": [
    {
        "name": "workspace 1",
        "roles": [
            {
                "name": "workspace:admin",
                "users": [
                    {
                        "id": "user1@email.com"
                    },
                    {
                        "id": "user2@email.com"
                    }
                ],
                "permissions": [
                    "create",
                    "delete",
                    "download"
                ]
            }
        ],
        "projects": [
            {
                "id": 0001,
                "name": "project 1",
                "folders": [2001, 2002, 2003]
            }
        ]
    }
]}

I’m trying to format the document so that the result looks like the following. The idea is return all roles and workspace.roles in which a particular user is in (excluding the user array).

{
"id": "23234sdsdf-23234sds-2343sdfsdf",
"type": "organization",
"name": "org1",
"roles": [
    {
        "name": "org:admin",
        "permissions": [
            "create",
            "delete"
        ]
    }
],
"workspaces": [
    {
        "name": "workspace 1",
        "roles": [
            {
                "name": "workspace:admin",
                "permissions": [
                    "create",
                    "delete",
                    "download"
                ]
            }
        ],
        "projects": [
            {
                "id": 0001,
                "name": "project 1",
                "folders": [2001, 2002, 2003]
            }
        ]
    }
]}

I’ve come up with the query below which returns all roles and workspace.roles, but I can’t seem to figure out how to loop through roles and workspace.roles to only include these properties when a user exists in the user array property.

SELECT 
 s.name,
 ARRAY {
  "name": r2.name,
  "permissions": r2.perm
 } FOR r2 WITHIN s.roles,
 ARRAY {
  "name": ws.name,
  "roles": ARRAY {
   "name": wsRole.name,
   "permissions": wsRole.perm
  } FOR wsRole IN ws.roles END,
  "projects": ws.projects
 } FOR ws IN s.workspaces END AS workspaces
FROM `org` s
USE KEYS 'org::8080'
WHERE s.type = 'organization'
AND (ANY u WITHIN s.roles[*].users SATISFIES u.id IN ["user2@email.com"] END
     OR ANY u WITHIN s.workspaces[*].roles[*].users SATISFIES u.id ["user2@email.com"] END)

Can anyone help?

Thanks!

2

In ARRAY construct you need to use WHEN clause to eliminate items

range-xform.png
range-xform.png?raw=true898×310 

SELECT
 s.name,
 ARRAY { "name": r.name, "permissions": r.perm }
      FOR r IN s.roles
      WHEN (ANY u IN r.users SATISFIES u.id IN ["user2@email.com"] END)
      END AS roles,
 ARRAY { "name": w.name, "projects": w.projects, "roles":  ARRAY { "name": r.name, "permissions": r.perm }
                                                           FOR r IN w.roles
                                                           WHEN (ANY u IN r.users SATISFIES u.id IN ["user2@email.com"] END) END
       }
      FOR w IN s.workspaces
      END AS workspaces
FROM `org` s
USE KEYS 'org::8080'
WHERE s.type = 'organization'
AND (ANY r IN s.roles SATISFIES (ANY u IN r.users SATISFIES u.id IN ["user2@email.com"] END) END OR
     ANY w IN s.workspaces SATISFIES (ANY r IN w.roles SATISFIES (ANY u IN w.users SATISFIES u.id IN ["user2@email.com"] END) END) END );
2 Likes
3

Thank you vsr1, that works really well.