azure-cosmosdb - 为什么此 CosmosDB 子查询会失败?
问题描述
我正在尝试使用一个简单的子查询来获取一个值,但我得到一个关于基数的错误
查询如下:
SELECT va.variantId AS ItemNo,
c.Season,
SUBSTRING(va.variantId, 0, 7) AS ProductNo,
SUBSTRING(va.variantId, 0, 10) AS ArticleNo,
SUBSTRING(va.variantId, 0, 13) AS VariantNo,
(
SELECT VALUE p["value"]
FROM c
JOIN p IN c.OriginalData.presentation.productNameLong
WHERE c.ItemNo = '123456'
AND p.locale = 'en-GB'
AND c.Season = '201808'
AND c.brand = 'xxx'
) AS Title
FROM c
JOIN sm IN c.OriginalData.base.sales.summary.salesMarkets
JOIN ar IN sm.articles
JOIN va IN ar.variants
JOIN ch IN va.channels
WHERE c.ItemNo = '123456'
AND sm.salesMarket = 'SE'
AND ch.channelName = 'xxx'
错误如下:
Failed to query item for container rawdata:
Gateway Failed to Retrieve Query Plan: Message: {"errors":[{"severity":"Error","location":{"start":227,"end":498},"code":"SC2201","message":"The cardinality of a scalar subquery result set cannot be greater than one."}]}
ActivityId: 11ff3b08-d8a7-4737-9111-81f319cf1dc5, Microsoft.Azure.Documents.Common/2.11.0, Microsoft.Azure.Documents.Common/2.11.0
该子查询的结果只能是 1 个单一结果,所以我不确定它在抱怨什么
解决方案
您需要使用 ARRAY 表达式从子查询的结果构造一个数组。
请尝试以下 SQL:
SELECT va.variantId AS ItemNo,
c.Season,
SUBSTRING(va.variantId, 0, 7) AS ProductNo,
SUBSTRING(va.variantId, 0, 10) AS ArticleNo,
SUBSTRING(va.variantId, 0, 13) AS VariantNo,
ARRAY(
SELECT VALUE p["value"]
FROM c
JOIN p IN c.OriginalData.presentation.productNameLong
WHERE c.ItemNo = '123456'
AND p.locale = 'en-GB'
AND c.Season = '201808'
AND c.brand = 'xxx'
) AS Title
FROM c
JOIN sm IN c.OriginalData.base.sales.summary.salesMarkets
JOIN ar IN sm.articles
JOIN va IN ar.variants
JOIN ch IN va.channels
WHERE c.ItemNo = '123456'
AND sm.salesMarket = 'SE'
AND ch.channelName = 'xxx'
推荐阅读
- python - 使用熊猫查找两个特定日期之间月份的最后一天和第一天
- azure - Azure 嵌套虚拟化网络
- java - 如何在 Spring Boot 中的 H2-Database 中插入值
- rust - 如何让一个可选依赖项启用另一个可选依赖项 rust
- java - 比较 List 对象的元素
- angular - NGX Bootstrap Typeahead,一次有多个模板
- reactjs - 在反应中传递带有上下文的状态
- java - 如何使用 lombok builder 更新实体?
- python - DRF 无效的 DateTimeField 值
- c# - 当它与我的 textBox1.text 上提供的输入匹配时,如何编写命令从数据库表中选择数据?