How can I make a select * query work in CosmosDB with a join?

Question

I have documents who look similar to this

{
    "id": "XX",
    "_id": "XX",
    "artno": "0107727021",
    "vendor": "XX",
    "updatedAt": "2019-06-24T20:25:49.602Z",
    "locales": [
        {
            "title": "Bademantel aus Leinen",
            "description": "PREMIUM QUALITÄT. Bademantel aus gewaschenem Leinen mit zwei Vordertaschen und einem Bindegürtel in der Taille. Unisex. Durch Trocknen im Wäschetrockner bleibt die Weichheit des Leinens erhalten.",
            "categories": [
                "Damen",
                "Nachtwäsche",
                "Nachthemden & Morgenmäntel",
                "Bademantel"
            ],
            "brand": "XX",
            "country": "DE",
            "currency": "EUR",
            "language": "de",
            "variants": [
                {
                    "artno": "0107727021002",
                    "price": 39.99,
                    "stock": 1,
                    "attributes": {
                        "size": "S/M",
                        "color": "Grau"
                    }
                },
                {
                    "artno": "0107727021004",
                    "price": 39.99,
                    "stock": 0,
                    "attributes": {
                        "size": "L/XL",
                        "color": "Grau"
                    }
                }
            ]
        },
        {
            "title": "Morgonrock i tvättat linne",
            "description": "PREMIUM QUALITY. En morgonrock i tvättat linne. Morgonrocken har två framfickor och knytskärp i midjan. Unisex. Torktumla gärna för att behålla mjukheten i linnet.",
            "categories": [
                "Dam",
                "Sovplagg",
                "Nattlinnen & Morgonrockar",
                "Morgonrock"
            ],
            "brand": "XX",
            "country": "SE",
            "currency": "SEK",
            "language": "sv",
            "variants": [
                {
                    "artno": "0107727021002",
                    "price": 399,
                    "stock": 1,
                    "attributes": {
                        "size": "S/M",
                        "color": "Grå"
                    }
                },
                {
                    "artno": "0107727021004",
                    "price": 399,
                    "stock": 0,
                    "attributes": {
                        "size": "L/XL",
                        "color": "Grå"
                    }
                }
            ]
        }
    ]
}

I want to query and get all docs but only with the data from the part of the array where specified country is the one I am filtering on.

For example country = 'DE' then I want the document with only that part of the array not all countries information

I tried the following query but it tells me I cannot use select *

SELECT * FROM c join l in c.locales where l.country = 'DE'

So what can I do in order to make this work?

Jay Gong Jay Gong · Accepted Answer · 2019-06-26T09:24:25

The error indicates that 'SELECT *' is only valid with a single input set.

Your sql uses JOIN,so please define the specific columns you want to query:

SELECT c.XX,c.YY,l.ZZ FROM c
 join l in c.locales 
 where l.country = 'DE'

Or use c directly:

SELECT c FROM c
 join l in c.locales 
 where l.country = 'DE'

Please adjust your sql like:

SELECT l FROM c
 join l in c.locales 
 where l.country = 'DE'

Output:

[
    {
        "l": {
            "title": "Bademantel aus Leinen",
            "description": "PREMIUM QUALITÄT. Bademantel aus gewaschenem Leinen mit zwei Vordertaschen und einem Bindegürtel in der Taille. Unisex. Durch Trocknen im Wäschetrockner bleibt die Weichheit des Leinens erhalten.",
            "categories": [
                "Damen",
                "Nachtwäsche",
                "Nachthemden & Morgenmäntel",
                "Bademantel"
            ],
            "brand": "XX",
            "country": "DE",
            "currency": "EUR",
            "language": "de",
            "variants": [
                {
                    "artno": "0107727021002",
                    "price": 39.99,
                    "stock": 1,
                    "attributes": {
                        "size": "S/M",
                        "color": "Grau"
                    }
                },
                {
                    "artno": "0107727021004",
                    "price": 39.99,
                    "stock": 0,
                    "attributes": {
                        "size": "L/XL",
                        "color": "Grau"
                    }
                }
            ]
        }
    }
]

How can I make a select * query work in CosmosDB with a join?

1 Answers