How to make this CosmosDB SQL Query work without knowing ARRAY index?

Question

I am querying a CosmosDB in such a way that I am getting a string in and ned to return some data out through a C# WEB API, the query that works for me is as below

SELECT *
FROM c IN jongel.OriginalData.base.sales.variants
WHERE c.globalTradeItemNumber.globalTradeItemNumberType[0].GTIN = '1111111111111'

The problem is that I have to know the ARRAY INDEX for the globalTradeItemNumberType ARRAY, [0] in this example, for it to work but it is not always 0, it could be any number from 0-9 basically and I cannot figure out how to rewrite the query so that it works regardless of the index where the matching data is found?

How can I rewrite this query so that I do not need to know the ARRAY INDEX beforehand?

--- EDIT ---

A sample document shortened to only include the needed parts

{
    "id": "635af816-8db7-49c6-8284-ab85116b499b",
    "brand": "XXX",
    "IntegrationSource": "XXX",
    "DocumentType": "Item",
    "ItemInformationType": "",
    "ItemLevel": "Article",
    "ItemNo": "0562788040",
    "UpdatedDate": "1/1/2020 4:00:01 AM",
    "UpdatedDateUtc": "2020-01-01T04:00:01.82Z",
    "UpdatedBy": "XXX",
    "OriginalData": {
        "corporateBrandId": "2",
        "productId": "0562788",
        "articleId": "0562788040",
        "season": "201910",
        "base": {
            "sales": {
                "SAPArticleNumber": "562788040190",
                "simpleColour": {
                    "simpleColourId": "99",
                    "simpleColourDescription": "Green",
                    "translatedColourDescription": [
                        {
                            "languageCode": "sr",
                            "simpleColourDescription": "Zeleno"
                        },
                        {
                            "languageCode": "zh-Hans",
                            "simpleColourDescription": "绿色"
                        },
                        {
                            "languageCode": "vi-VN",
                            "simpleColourDescription": "Xanh la cay"
                        }
                    ]
                },
                "variants": [
                    {
                        "variantId": "0562788040001",
                        "variantNumber": "562788040190001",
                        "variantDescription": "YYYYYYYYY, XXS",
                        "sizeScaleAndCode": "176-001",
                        "netWeight": 0.491,
                        "unitsOfMeasure": {
                            "unitsOfMeasureType": [
                                {
                                    "alternativeUOM_ISO": "PCE",
                                    "length": 320,
                                    "width": 290,
                                    "height": 31,
                                    "unitOfDimension": "MM",
                                    "volume": 2876.8,
                                    "volumeUnit": "CCM",
                                    "weightUnit": "KG"
                                }
                            ]
                        },
                        "globalTradeItemNumber": {
                            "globalTradeItemNumberType": [
                                {
                                    "GTIN": "1111111111111",
                                    "GTINCategory": "Z3"
                                },
                                {
                                    "GTIN": "2222222222222",
                                    "GTINCategory": "Z3"
                                },
                                {
                                    "GTIN": "3333333333333",
                                    "GTINCategory": "IE"
                                }
                            ]
                        }
                    }
                ]
            }
        }
    }
}

I tried the following query based on suggested answer below but it did not work

  SELECT *
FROM c 
WHERE ARRAY_CONTAINS(c.OriginalData.base.sales.variants.globalTradeItemNumber.globalTradeItemNumberType, {GTIN:"1111111111111"}, true)

I guess the above fails because variants part of the tree is also an array?

NOTE: the variants array can hold several objects so its not always index[0]

@user1672994 I tried joining to the array but then I got 0 hits all the time must have done it wrong — Matt Douhan

Paul Paul · Accepted Answer · 2019-12-31T12:14:17

You could try using the ARRAY_CONTAINS function.

SELECT *
FROM c IN jongel.OriginalData.base.sales.variants
WHERE ARRAY_CONTAINS(c.globalTradeItemNumber.globalTradeItemNumberType, {GTIN:"1111111111111"}, true)

This will allow the query to search all items in the array for a matching GTIN value.

https://docs.microsoft.com/en-us/azure/cosmos-db/sql-query-array-contains

How to make this CosmosDB SQL Query work without knowing ARRAY index?

1 Answers