Perform ARRAY_REPLACE on nested array of objects

SQL++
1

I have a document with the following structure:

{
"accounts": [
    {
    "assignments": [
            {
                "roleId": 2,
                "userId": 16
            },
            {
                "roleId": 600,
                "userId": 9266
            }
        ],
    "id": 101,
    "name": "First Sample Account",
    "workspaces": [
        {
        "assignments": [
            {
                "roleId": 3,
                "userId": 16
            },
            {
                "roleId": 600,
                "userId": 9266
            }
        ],
        "id": 102,
        "name": "First Sample Workspace"
        },
        {
        "assignments": [
            {
                "roleId": 3,
                "userId": 16
            },
            {
                "roleId": 600,
                "userId": 9266
            }
        ],
        "id": 103,
        "name": "Second Sample Workspace"
        }
    ]
    },
    {
    "assignments": [
            {
                "roleId": 2,
                "userId": 16
            },
            {
                "roleId": 600,
                "userId": 9266
            }
        ],
    "id": 104,
    "name": "Second Sample Account",
    "workspaces": [
        {
        "assignments": [
            {
                "roleId": 3,
                "userId": 16
            },
            {
                "roleId": 600,
                "userId": 9266
            }
        ],
        "id": 105,
        "name": "Third Sample Workspace"
        },
        {
        "assignments": [
            {
                "roleId": 3,
                "userId": 16
            },
            {
                "roleId": 600,
                "userId": 9266
            }
        ],
        "id": 106,
        "name": "Fourth Sample Workspace"
        }
    ]
    }
],
"assignments": [
    {
        "roleId": 4,
        "userId": 16
    },
    {
        "roleId": 600,
        "userId": 9266
    }
],
"id": 100,
"name": "Sample Organization",
"type": "organization"
}

I want to perform an update, finding all instances of the assignment object with userId = 9266 and replace them with { "userId": 9266, "roleId": 1336}. Ideally, the resulting document would look like this:

{
"accounts": [
    {
    "assignments": [
            {
                "roleId": 2,
                "userId": 16
            },
            {
                "roleId": 1336,
                "userId": 9266
            }
        ],
    "id": 101,
    "name": "First Sample Account",
    "workspaces": [
        {
        "assignments": [
            {
                "roleId": 3,
                "userId": 16
            },
            {
                "roleId": 1336,
                "userId": 9266
            }
        ],
        "id": 102,
        "name": "First Sample Workspace"
        },
        {
        "assignments": [
            {
                "roleId": 3,
                "userId": 16
            },
            {
                "roleId": 1336,
                "userId": 9266
            }
        ],
        "id": 103,
        "name": "Second Sample Workspace"
        }
    ]
    },
    {
    "assignments": [
            {
                "roleId": 2,
                "userId": 16
            },
            {
                "roleId": 1336,
                "userId": 9266
            }
        ],
    "id": 104,
    "name": "Second Sample Account",
    "workspaces": [
        {
        "assignments": [
            {
                "roleId": 3,
                "userId": 16
            },
            {
                "roleId": 1336,
                "userId": 9266
            }
        ],
        "id": 105,
        "name": "Third Sample Workspace"
        },
        {
        "assignments": [
            {
                "roleId": 3,
                "userId": 16
            },
            {
                "roleId": 1336,
                "userId": 9266
            }
        ],
        "id": 106,
        "name": "Fourth Sample Workspace"
        }
    ]
    }
],
"assignments": [
    {
        "roleId": 4,
        "userId": 16
    },
    {
        "roleId": 1336,
        "userId": 9266
    }
],
"id": 100,
"name": "Sample Organization",
"type": "organization"
}

I would like to do this in a single update query, something like:

UPDATE users AS org
SET
  org.assignments = ARRAY_REPLACE(org.assignments, assignment, { "userId": 9266, "roleId": 1336})
      FOR assignment IN org.assignments WHEN assignment.userId = 9266 END,
  account.assignments = ARRAY_REPLACE(account.assignments, assignment, { "userId": 9266, "roleId": 1336 })
      FOR account IN org.accounts END,
  workspace.assignments = ARRAY_REPLACE(workspace.assignments, assignment, { "userId": 9266, "roleId": 1336})
      FOR workspace IN ARRAY_FLATTEN(ARRAY account.workspaces FOR account IN org.accounts END, 1) END
WHERE org.type = 'organization'

However, as you might guess from looking at that query, while the org roles get updated correctly, I have not figured out how to set the assignment for account and workspace so it is only for assignments where userId = 9266

I have tried the following query:

UPDATE users AS org
SET
    org.assignments = ARRAY_REPLACE(org.assignments, assignment, { "userId": 9266, "roleId": 1336})
        FOR assignment IN org.assignments WHEN assignment.userId = 9266 END,
    account.assignments = (
            ARRAY ARRAY_REPLACE(account.assignments, assignment, { "userId": 9266, "roleId": 1336})
            FOR assignment IN account.assignments WHEN assignment.userId = 9266 END
        )
        FOR account IN org.accounts END,
    workspace.assignments = (
            ARRAY ARRAY_REPLACE(workspace.assignments, assignment, { "userId": 9266, "roleId": 1336})
            FOR assignment IN workspace.assignments WHEN assignment.userId = 9266 END
        )
        FOR workspace IN ARRAY_FLATTEN(ARRAY account.workspaces FOR account IN org.accounts END, 1) END
WHERE org.type = 'organization'

However, this created an array of arrays and did not save changes per sub-object

I do not care if the final result doesn’t user ARRAY_REPLACE - if there is a way tyo just find all matching assignments and set roleId = 1336, that will work just as well. In this particular query, it is virtually guaranteed that there will always be an assignment that matches in each array - if there isn’t, then our system has screwed something up (in this particular usecase). However, it would be nice if the query was robust enough that, should it find an array of assignments that doesn’t have a matching user ID, it just doesn’t update it.

Any help would be appreciated.

2

So I found a query that will accomplish this:

UPDATE users AS org
SET
    orgAssignment.roleId = 1336
        FOR orgAssignment IN org.assignments WHEN orgAssignment.userId = 9266 END,
    accountAssignment.roleId = 1336
        FOR accountAssignment IN ARRAY_FLATTEN(
            array account.assignments FOR account IN org.accounts END, 
            1) WHEN accountAssignment.userId = 9266 END,
    workspaceAssignment.roleId = 1336
        FOR workspaceAssignment IN ARRAY_FLATTEN(
            ARRAY workspace.assignments FOR workspace IN ARRAY_FLATTEN(
                ARRAY account.workspaces FOR account IN org.accounts END, 
                1)
            END,
            1)
        WHEN workspaceAssignment.userId = 9266 END
WHERE org.type = 'organization'
1 Like
3

You should try following simplified one.

UPDATE users AS org
SET oa.roleId = 1336 FOR oa IN org.assignments WHEN oa.userId = 9266 END,
    ass.roleId = 1336 FOR ass IN acc.assignments FOR acc IN org.accounts WHEN ass.userId = 9266 END,
    wk.roleId = 1336 FOR wk IN acc.workspaces FOR acc IN org.accounts WHEN wk.userId = 9266 END
WHERE org.type = 'organization' AND ANY v WITHIN org SATISFIES v.userId = 9266 END;