azure-cosmosdb - 如何编写一个查看所有数组条目的查询？
问题描述
如何编写允许我在数组的所有条目中查找条目的 CosmosDB 查询或 SPR？其中每个数组条目是一个复杂的数据类型，而不仅仅是一个简单的数组？
我试过使用这个查询：
SELECT DISTINCT c.locales[0].categories[0] AS topLevelCategory
FROM c
WHERE c.locales[0].country = 'SE'
问题是我不能保证 c.locales[0] 有时总是'SE'，这是数组中的第二个或第三个条目，所以我不知道 c.locales[X] 应该是什么，其中 X 代表我的语言环境需要，例如如果 c.locales[0] 是 'SE' 那么我不会得到我想要的命中，因为 'SE' 恰好是 c.locales[1]
如何绕过这个限制？我需要能够首先应用 country = 'SE' 以获得正确的数组条目，然后从该数组条目中获取类别。
我只是无法弄清楚如何做到这一点？
下面的示例文档和预期的输出将以最简单的输出格式为“Herr”。
{
"id": "0570eca0-8f16-4c85-a985-e3a271bcc6bc",
"_id": "5b07c2bfbc7407000122e8b4",
"artno": "0614460008",
"vendor": "Acme",
"updatedAt": "2019-06-25T18:50:33.167Z",
"locales": [
{
"title": "Gestreiftes T-Shirt",
"description": "Gestreiftes T-Shirt aus Baumwolljersey.",
"categories": [
"Herren",
"Große Größen",
"T-Shirts & Tanktops",
"T-Shirt"
],
"brand": null,
"images": [
],
"country": "DE",
"currency": "EUR",
"language": "de",
"variants": [
{
"artno": "0614460008005",
"urls": [
],
"price": 9.99,
"stock": 1,
"attributes": {
"size": "XL",
"color": "Schwarz/Weiß gestreift"
}
},
{
"artno": "0614460008002",
"urls": [
],
"price": 9.99,
"stock": 1,
"attributes": {
"size": "S",
"color": "Schwarz/Weiß gestreift"
}
},
{
"artno": "0614460008004",
"urls": [
],
"price": 9.99,
"stock": 1,
"attributes": {
"size": "L",
"color": "Schwarz/Weiß gestreift"
}
},
{
"artno": "0614460008001",
"urls": [
],
"price": 9.99,
"stock": 1,
"attributes": {
"size": "XS",
"color": "Schwarz/Weiß gestreift"
}
},
{
"artno": "0614460008006",
"urls": [
],
"price": 9.99,
"stock": 1,
"attributes": {
"size": "XXL",
"color": "Schwarz/Weiß gestreift"
}
},
{
"artno": "0614460008003",
"urls": [
],
"price": 9.99,
"stock": 1,
"attributes": {
"size": "M",
"color": "Schwarz/Weiß gestreift"
}
}
]
},
{
"title": "Striped T-shirt",
"description": "Striped T-shirt in cotton jersey.",
"categories": [
"Men",
"T-shirts & Vests",
"Short Sleeve",
"T-shirt"
],
"brand": null,
"images": [
],
"country": "UK",
"currency": "GBP",
"language": "en",
"variants": [
{
"artno": "0614460008006",
"urls": [
],
"price": 8.99,
"stock": 1,
"attributes": {
"size": "XXL",
"color": "Black/White striped"
}
},
{
"artno": "0614460008005",
"urls": [
],
"price": 8.99,
"stock": 1,
"attributes": {
"size": "XL",
"color": "Black/White striped"
}
},
{
"artno": "0614460008004",
"urls": [
],
"price": 8.99,
"stock": 1,
"attributes": {
"size": "L",
"color": "Black/White striped"
}
},
{
"artno": "0614460008002",
"urls": [
],
"price": 8.99,
"stock": 1,
"attributes": {
"size": "S",
"color": "Black/White striped"
}
},
{
"artno": "0614460008003",
"urls": [
],
"price": 8.99,
"stock": 1,
"attributes": {
"size": "M",
"color": "Black/White striped"
}
},
{
"artno": "0614460008001",
"urls": [
],
"price": 8.99,
"stock": 1,
"attributes": {
"size": "XS",
"color": "Black/White striped"
}
}
]
},
{
"title": "Randig t-shirt",
"description": "En randig t-shirt i bomullstrikå.",
"categories": [
"Herr",
"T-shirts & Linnen",
"Kortärmat",
"T-shirt"
],
"brand": null,
"images": [
],
"country": "SE",
"currency": "SEK",
"language": "sv",
"variants": [
{
"artno": "0614460008001",
"urls": [
],
"price": 99,
"stock": 1,
"attributes": {
"size": "XS",
"color": "Svart/Vitrandig"
}
},
{
"artno": "0614460008001",
"urls": [
],
"price": 99,
"stock": 1,
"attributes": {
"size": "XL",
"color": "Svart/Vitrandig"
}
},
{
"artno": "0614460008001",
"urls": [
],
"price": 99,
"stock": 1,
"attributes": {
"size": "S",
"color": "Svart/Vitrandig"
}
},
{
"artno": "0614460008001",
"urls": [
],
"price": 99,
"stock": 1,
"attributes": {
"size": "XXL",
"color": "Svart/Vitrandig"
}
},
{
"artno": "0614460008001",
"urls": [
],
"price": 99,
"stock": 1,
"attributes": {
"size": "M",
"color": "Svart/Vitrandig"
}
},
{
"artno": "0614460008001",
"urls": [
],
"price": 99,
"stock": 1,
"attributes": {
"size": "L",
"color": "Svart/Vitrandig"
}
}
]
},
{
"title": "Striped T-shirt",
"description": "Striped T-shirt in cotton jersey.",
"categories": [
"Men",
"T-shirts & Tank tops",
"Short Sleeves",
"T-shirt"
],
"brand": null,
"images": [
],
"country": "US",
"currency": "USD",
"language": "en",
"variants": [
{
"artno": "0614460008001",
"urls": [
],
"price": 9.99,
"stock": 0,
"attributes": {
"size": "S",
"color": "Black/white striped"
}
},
{
"artno": "0614460008001",
"urls": [
],
"price": 9.99,
"stock": 0,
"attributes": {
"size": "XL",
"color": "Black/white striped"
}
},
{
"artno": "0614460008001",
"urls": [
],
"price": 9.99,
"stock": 0,
"attributes": {
"size": "M",
"color": "Black/white striped"
}
},
{
"artno": "0614460008001",
"urls": [
],
"price": 9.99,
"stock": 0,
"attributes": {
"size": "L",
"color": "Black/white striped"
}
},
{
"artno": "0614460008001",
"urls": [
],
"price": 9.99,
"stock": 0,
"attributes": {
"size": "XXL",
"color": "Black/white striped"
}
},
{
"artno": "0614460008001",
"urls": [
],
"price": 9.99,
"stock": 1,
"attributes": {
"size": "XS",
"color": "Black/white striped"
}
}
]
}
],
"relatedArtno": [
"0614460001",
"0614460002",
"0614460005",
"0614460006",
"0614460007",
"0614460011",
"0614460012"
],
"_rid": "QEwcAMCVWqgGAAAAAAAAAA==",
"_self": "dbs/QEwcAA==/colls/QEwcAMCVWqg=/docs/QEwcAMCVWqgGAAAAAAAAAA==/",
"_etag": "\"2e00f1ca-0000-0c00-0000-5d144d660000\"",
"_attachments": "attachments/",
"_ts": 1561611622
}
解决方案
Join在你的 sql 中使用：
SELECT locales.categories[0] AS topLevelCategory
FROM c
join locales in c.locales
WHERE locales.country = 'SE'
输出：
更新答案：
两点。
一，您的语言环境也是一个数组，因此您无法使用 访问类别c.locales.categories。您需要使用 查询类别c.locales[index].categories。
二、Array_contains 有第三个参数。如果要过滤具有部分条件的数组，则需要将第三个参数添加为 false。
ARRAY_CONTAINS(c.locales[index].categories[0], 'kids',true)
更多详细信息，请参阅文档。
