How to query CosmosDB when the Where clause data is in a subarray?

Question

I have the following document

{
    "id": "46c0ccbc-7a05-41dc-bc33-e9d1e69b74fa",
    "_id": "5bf8b90f9568cf0001463719",
    "vendor": "XXX",
    "updatedAt": "2018-11-26T02:17:59.311Z",
    "locales": [
        {
            "title": "351011",
            "description": " ",
            "categories": [
                "Children",
                "Accessories"
            ],
            "brand": null,
            "images": [
                "https://lp.example.com/app006prod?set=source[02_0690165_001_001],type[PRODUCT],device[hdpi],quality[80],ImageVersion[2018081]&call=url[file:/product/main]"
            ],
            "country": "SE",
            "currency": "SEK",
            "language": "en",
            "variants": [
                {
                    "artno": "example",
                    "urls": [
                        "https://www.example.com/en_sek/children/baby-newborn/product.cotton-cashmere-newborn-mittens-red.0690165001.html"
                    ],
                    "price": 120,
                    "stock": 15,
                    "attributes": {
                        "size": "One Size",
                        "color": "Burgundy"
                    }
                }
            ]
        }
    ],
    "_rid": "QEwcAOOf+rUHAAAAAAAAAA==",
    "_self": "dbs/QEwcAA==/colls/QEwcAOOf+rU=/docs/QEwcAOOf+rUHAAAAAAAAAA==/",
    "_etag": "\"00000e00-0000-0000-0000-5bfc890d0000\"",
    "_attachments": "attachments/",
    "_ts": 1543276813
}

I am trying to write a simple query that lists all documents that has "Children" as one of its Categories

I tried the following query but it does not work

SELECT * FROM c where c.locales.categories = "Children"

I am assuming I am referencing the subarrays wrong but what is the right way to write this query?

Nick Chapsas · Accepted Answer · 2018-11-27 08:00:21Z

1

You need to use the ARRAY_CONTAINS function.

Try this: SELECT * FROM c where ARRAY_CONTAINS(c.locales[0].categories, "Children")

answered Nov 27, 2018 at 8:00

Nick Chapsas

7,2501 gold badge22 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Matt Douhan Over a year ago

ANd you used locales[0] because it was the first sub array if I wanted the images I would do locales[1] ?

Nick Chapsas Over a year ago

No you would still do c.locales[0].images. It's just the way i found CosmosDB SQL working to do this thing.

Matt Douhan Over a year ago

ok but then what if I wanted to search on the artno under the variants array? there are dicts under there how do I address them?

Nick Chapsas Over a year ago

c.locales[0].variants[0].artno

Collectives™ on Stack Overflow

How to query CosmosDB when the Where clause data is in a subarray?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related