Query to get specific item in an array

I have two queries that work in Cosmos. But now I want to combine them into one query. How do I do that?

"id": "LanguageList-V3",
       ...
        "Languages": [
            {
                "languageId": "af",
                "englishName": "Afrikaans",
                "countryCode": null,
                "flagUrl": null,
                "UiId": "af",
                "nativeName": "Afrikaans",
                "direction": "ltr",
                "learnEnglishFromEnglish": "Learn English from Afrikaans",
                "iSpeakX": "Ek praat Afrikaans",
                "learnEnglishFromNative": "Leer Engels uit Afrikaans",
                "ignore": false,
                "active": false
            },

Query 1: select * from c where c.id = "LanguageList-V3"

This return the entire document which has a long list of Languages in an array, but only from the specified document. (There are other version of the same list in the table)

Query 2: Return just the one language object I need. This works, but it return it for all of the version, not just the current version.

SELECT * FROM
    c IN c.Languages
WHERE c.languageId = "af"

How do I combine the two together? I have tried, but unable to get the two to work together.

Solution

I have two queries that work in Cosmos. But now I want to combine them into one query. How do I do that?

As you want to combine the two queries, in the first query it is retrieving only the data which is "id = LanguageList-V3" and in the second query it is retrieving the data having languageId = "af". Means after combining two queries using JOIN, it will print only the data of id having LanguageList-V3 and with languageId = "af" .

Also, you mentioned There are other version of the same list in the table, below is the data stored in my cosmos db container with some other versions along with id "LanguageList-V3" such as "LanguageList-V1" and "LanguageList-V2":

[
    {
        "id": "LanguageList-V1",
        "type": "LanguageList",
        "version": 1,
        "createdAt": "2023-01-01T00:00:00Z",
        "Languages": [
            {
                "languageId": "af",
                "englishName": "Afrikaans",
                "nativeName": "Afrikaans",
                "active": true
            },
            {
                "languageId": "es",
                "englishName": "Spanish",
                "nativeName": "Español",
                "active": true
            }
        ]
    },
    {
        "id": "LanguageList-V2",
        "type": "LanguageList",
        "version": 2,
        "createdAt": "2024-01-01T00:00:00Z",
        "Languages": [
            {
                "languageId": "es",
                "englishName": "Spanish",
                "nativeName": "Español",
                "active": false
            },
            {
                "languageId": "zh",
                "englishName": "Chinese",
                "nativeName": "中文",
                "active": true
            }
        ]
    },
    {
        "id": "LanguageList-V3",
        "type": "LanguageList",
        "version": 3,
        "createdAt": "2025-04-21T12:00:00Z",
        "Languages": [
            {
                "languageId": "af",
                "englishName": "Afrikaans",
                "nativeName": "Afrikaans",
                "active": false
            },
            {
                "languageId": "zh",
                "englishName": "Chinese",
                "nativeName": "中文",
                "active": true
            }
        ]
    }
]

Below is the query which i tried to combine both the queries using JOIN:

SELECT VALUE l
FROM c
JOIN l IN c.Languages
WHERE c.id = "LanguageList-V3" AND l.languageId = "af"

Output:

[
    {
        "languageId": "af",
        "englishName": "Afrikaans",
        "nativeName": "Afrikaans",
        "active": false
    }
]

Note: Cosmos DB does not support joins across different containers or items. It supports self-join ,means joins occur within a single item. For more information, please refer to this link.