Get all records that have an item in an array with certain email-CodePudding

I am trying to write a CosmosDB query to return all records in a table that have an item in the Users array that has an email of a certain value. Here is roughly what a full record looks like.

    {
    "year": "2022",
    "id": "d992b109-d94a-4d75-9ebb-9ef4e5e851ae",
    "partitionKey": "2022",
    "LeagueName": "Hipster Kitties",
    "Users": [
        {
            "Id": "eb71e719-4bc1-4f32-9767-e5dc20b2e63f",
            "UserName": "[email protected]",
            "NormalizedUserName": "[email protected]",
            "Email": "[email protected]",
            "NormalizedEmail": "[email protected]",
            "EmailConfirmed": false,
            "PhoneNumber": null,
            "PhoneNumberConfirmed": false,
            "TwoFactorEnabled": false,
            "LockoutEnd": null,
            "LockoutEnabled": true,
            "AccessFailedCount": 0
        },
        {
            "Id": "eb71e719-4bc1-4f32-9767-e5dc20sdfsdf",
            "UserName": "[email protected]",
            "NormalizedUserName": "[email protected]",
            "Email": "[email protected]",
            "NormalizedEmail": "[email protected]",
            "EmailConfirmed": false,
            "PhoneNumber": null,
            "PhoneNumberConfirmed": false,
            "TwoFactorEnabled": false,
            "LockoutEnd": null,
            "LockoutEnabled": true,
            "AccessFailedCount": 0
        }
    ],
    "LeagueCreator": {
        "Id": "eb71e719-4bc1-4f32-9767-e5dc20b2e63f",
        "UserName": "[email protected]",
        "NormalizedUserName": "[email protected]",
        "Email": "[email protected]",
        "NormalizedEmail": "[email protected]",
        "EmailConfirmed": false,
        "PhoneNumber": null,
        "PhoneNumberConfirmed": false,
        "TwoFactorEnabled": false,
        "LockoutEnd": null,
        "LockoutEnabled": true,
        "AccessFailedCount": 0
    },
    "LeagueAdmins": [
        {
            "Id": "eb71e719-4bc1-4f32-9767-e5dc20b2e63f",
            "UserName": "[email protected]",
            "NormalizedUserName": "[email protected]",
            "Email": "[email protected]",
            "NormalizedEmail": "[email protected]",
            "EmailConfirmed": false,
            "PhoneNumber": null,
            "PhoneNumberConfirmed": false,
            "TwoFactorEnabled": false,
            "LockoutEnd": null,
            "LockoutEnabled": true,
            "AccessFailedCount": 0
        }
    ],
    "IsPublic": false,
    "Seasons": [],
    "Type": 1,
    "Settings": {
        "TotalPicks": 6,
        "KeyPicks": 1,
        "KeyPickBonus": 1,
        "WeekStartingMoney": 0,
        "MinimumGamesToPick": 0
    },
    "_attachments": "attachments/",
    "_ts": 1665116599
}

Here is the query I have so far which will get me the id's, but I can't get the full records.

    SELECT c.id
    FROM c
    JOIN t in c.Users
    WHERE t.Email = "[email protected]"

If I execute the following query, I get a SQL syntax error.

SELECT *
FROM c
WHERE c.id IN (SELECT d.id
FROM d
JOIN t in d.Users
WHERE t.Email = "[email protected]")

Message: {"errors":[{"severity":"Error","location":{"start":33,"end":39},"code":"SC1001","message":"Syntax error, incorrect syntax near 'SELECT'."}]} ActivityId: , Microsoft.Azure.Documents.Common/2.14.0

Worst case, I could get back these Id's and then run another query which would get all records with those Id's, but want to see if I can run it in one query instead of two.

CodePudding user response：

You can't just SELECT *. You can specify individual properties though. For example:

SELECT c.id, c.LeagueName, t.UserName, t.Email
FROM c
JOIN t in c.Users
WHERE t.Email = "[email protected]"

This would return something like:

[
    {
        "id": "d992b109-d94a-4d75-9ebb-9ef4e5e851ae",
        "LeagueName": "Hipster Kitties",
        "UserName": "[email protected]",
        "Email": "[email protected]"
    }
]