Azure Cosmos SQL DB中通过JOIN提取数组单个元素的方法

原始查询及返回结果 #

我原本使用以下查询语句：

SELECT c.mainColour,t AS colour 
FROM c
JOIN t IN c.creatorStyleMainColours
WHERE c.creatorDefaultStyleProfile = "test"

该查询返回包含完整colour对象结构的结果：

[
    {
        "colour": {
            "mainColour": "black",
            "colourPairings": [
                "orange",
                "silver",
                "gold"
            ]
        }
    }
]

需求 #

我需要仅返回mainColour字段的数值，得到类似["black","beige","white"]的简单数组。

尝试的错误查询 #

我尝试了以下语句，但出现语法错误：

SELECT VALUE c.mainColour,t AS colour 
FROM c
JOIN t IN c.creatorStyleMainColours
WHERE c.creatorDefaultStyleProfile = "test"

完整文档内容 #

{
    "accountId": "59951c4d-1f0b-483b-a020",
    "creatorDefaultStyleProfile": "test",
    "creatorDefaultStyleGender": "Female",
    "creatorStyleAccountName": "",
    "creatorStyleCategory": [
        {
            "categoryCode": "fashion",
            "categoryName": "Fashion",
            "categoryMetadata": {
                "occasions": [
                    {
                        "occasionCode": "casual",
                        "occasionName": "Casual",
                        "occasionTypes": [
                            {
                                "occasionCode": "out",
                                "occasionName": "Everyday Wear"
                            }
                        ]
                    }
                ],
                "season": [
                    {
                        "seasonCode": "spring",
                        "seasonName": "Spring"
                    }
                ]
            },
            "id": "fashion"
        }
    ],
    "creatorStyleDescription": "",
    "creatorStyleId": "Classic_Default",
    "creatorStyleImages": [
        {
            "styleImageUrl": "https://4965fa50-6caf-11ed-8082e.png",
            "styleImageCategory": "fullsize",
            "styleImageResolution": {
                "width": 269.25,
                "height": 359
            },
            "styleImagePins": [
                {
                    "itemId": "F243906F37487C78D7EB99880142B370",
                    "vector": {
                        "x": 198.125,
                        "y": 44.33332824707031
                    }
                },
                {
                    "itemId": "9B543E0DC1B05F6E9AFD0A88285AEEF9",
                    "vector": {
                        "x": 52.791656494140625,
                        "y": 46.33332824707031
                    }
                },
                {
                    "itemId": "3E6464FC1F12E3C6E435F751438F1468",
                    "vector": {
                        "x": 120.45832824707031,
                        "y": 320.3333282470703
                    }
                },
                {
                    "itemId": "1CD6065A4C7A0BD66E44DC8A62448534",
                    "vector": {
                        "x": 115.45832824707031,
                        "y": 192
                    }
                }
            ]
        }
    ],
    "creatorStyleItems": [
        {
            "creatorStyleItemId": "9B543E0DC1B05F6E9AFD0A88285AEEF9",
            "ItemType": "Chest",
            "creatorStyleItemName": "t-shirt",
            "creatorStyleItemImages": [
                {
                    "imageUrl": "https://images/23660493-1-white?$XXL$",
                    "imageCategory": "thumbnail"
                }
            ]
        },
        {
            "creatorStyleItemId": "F243906F37487C78D7EB99880142B370",
            "ItemType": "Chest",
            "creatorStyleItemName": "chunky knit cardigan",
            "creatorStyleItemImages": [
                {
                    "imageUrl": "https://images/14399002-1-black?$XXL$",
                    "imageCategory": "thumbnail"
                }
            ]
        },
        {
            "creatorStyleItemId": "1CD6065A4C7A0BD66E44DC8A62448534",
            "ItemType": "Leg",
            "creatorStyleItemName": " mom jeans",
            "creatorStyleItemImages": [
                {
                    "imageUrl": "https://images-blue/202501450-1-denimblack?$XXL$",
                    "imageCategory": "thumbnail"
                }
            ]
        },
        {
            "creatorStyleItemId": "3E6464FC1F12E3C6E435F751438F1468",
            "ItemType": "Feet",
            "creatorStyleItemName": "trainers",
            "creatorStyleItemImages": [
                {
                    "imageUrl": "https://images./203112454-1-brown?$XXL$",
                    "imageCategory": "thumbnail"
                }
            ]
        }
    ],
    "creatorStyleMainColours": [
        {
            "mainColour": "black",
            "colourPairings": [
                "beige",
                "grey",
                "white"
            ]
        },
        {
            "mainColour": "beige",
            "colourPairings": [
                "black",
                "grey",
                "white"
            ]
        },
        {
            "mainColour": "white",
            "colourPairings": [
                "beige",
                "grey",
                "black"
            ]
        }
    ],
    "creatorStyleMerchantGroup": "test1",
    "creatorStyleName": "",
    "creatorStyleProfiles": [
        {
            "id": "",
            "profileAccountId": "test1",
            "profileId": "test1",
            "profileIsDefault": false,
            "profileName": "classic",
            "profileBiometric": {
                "biometricName": "",
                "biometricGender": "Female",
                "biometricHeight": [
               
                ],
                "biometricAgeRange": [
           
                ],
                "biometricHairColour": [
               
                ],
                "biometricSkinColour": [
                
                ],
                "biometricBodyShape": [
            
                ],
                "biometricMeasurement": {
          
                }
            },
            "profilePersona": {
                "personaName": "",
                "personaDressSense": [
    
                ],
                "personaInterests": [
                 
                ],
                "personaStyleIcons": [
                    
                ],
                "personaColours": [

                ]
            }
        }
    ],
    "creatorStyleStatus": {
        "styleStatusId": "nonpublished",
        "styleStatusDescription": ""
    },
    "id": "classicStyle1",
    "_rid": "IW43AJ08x+8BAAAAAAAAAA==",
    "_self": "dbs/IW43AA==/colls/IW43AJ08x+8=/docs/IW43AJ08x+8BAAAAAAAAAA==/",
    "_etag": "\"97005ae1-0000-1500-0000-638523be0000\"",
    "_attachments": "attachments/",
    "_ts": 1669669822
}

正确查询语句 #

要得到目标简单数组，需使用SELECT VALUE直接返回mainColour的数值，且注意从JOIN的t对象中取值（t是creatorStyleMainColours数组中的每个元素），正确语句如下：

SELECT VALUE t.mainColour
FROM c
JOIN t IN c.creatorStyleMainColours
WHERE c.creatorDefaultStyleProfile = "test"

该查询会返回：

["black","beige","white"]

内容的提问来源于stack exchange，提问作者JoRo