sql - 根据项目状态查询 Azure Cosmos DB 集合

Question

在 Azure 上的 Azure Cosmos DB 中，我有一个具有以下结构的集合：

[
  {
    "id": "1",
    "status": "wait",
    "order": {
            "orderId": "P1000",
            "orderPositionId": "1"
    }   
  },
  {
    "id": "2",
    "status": "wait",
    "order":{
            "orderId": "P1000",
            "orderPositionId": "2"
    }
  },
  {
    "id": "3",
    "status": "start",
    "order":{
            "orderId": "P1000",
            "orderPositionId": "1"
    }
  }
]

orderId 和 orderPositionId 的组合是唯一的。

我想查询状态为“等待”的所有项目，其中不存在状态为“开始”的项目。

对于上面的示例，查询的期望结果是：

[
{
    "id": "2",
    "status": "wait",
    "order":{
            "orderId": "P1000",
            "orderPositionId": "2"
    }
  }
]

我的想法是这样做（伪代码）

SELECT *FROM c
WHERE 
c.status="wait"
AND
EXISTS(SELECT * FROM c 
       WHERE c.order.orderId = c.order.orderId **(id of current item)** AND 
             c.order.orderPositionId=c.order.orderPositionId **(id of current item)** AND 
             c.status="start") = false

我知道这个想法不会这样。我是 Cosmos DB 和 SQL API 的新手，我无法找到可行的解决方案。我感谢有关如何解决此问题的任何提示。

谢谢，如果您需要更多信息，请告诉我。

标签： sqlazurenosqlazure-cosmosdbazure-cosmosdb-sqlapi

Answer 1

但我的目标是删除所有具有相应起始项的等待项。

因此，据此，如果有 2 个文件：

{
    "id": "1",
    "status": "wait",
    "order": {
            "orderId": "P1000",
            "orderPositionId": "1"
    }   
  },
  {
    "id": "3",
    "status": "start",
    "order":{
            "orderId": "P1000",
            "orderPositionId": "1"
    }
  }

他们有相同的：

 "order":{
            "orderId": "P1000",
            "orderPositionId": "1"
 }

然后你需要wait从wait家庭中删除这样的一个。

根据我的大量测试，我不能使用带有 array_contains 方法的子查询。所以我必须使用 2 sql 来实现你的要求。

第一个查询所有项目的sql start：

SELECT c['order'].orderId,c['order'].orderPositionId FROM c
where c.status = 'start'

你会得到数组结果：

然后在下面的长 sql 中使用该数组：

select c.id,c.status,c['order'] 
from c
where c.status = 'wait' and not EXISTS 
(select value c from c 
where c.status = 'wait' and arraycontains(
[
    {
        "orderId": "P1000",
        "orderPositionId": "1"
    }
],{"orderId": c['order'].orderId,"orderPositionId":c['order'].orderPositionId},true))

你可以得到输出：