sql - 用于获取尚未处理的通知的 SQL 查询

问题描述

我有一个 CosmosDb 容器，用于存储通过 SignalR 广播的通知。我已经存储了这两个通知：

[
    {
        "createdOn": "2019-12-18T09:48:21.4556896Z",
        "disposed": [
            {
                "disposedBy": "anotherAdmin@mail.com",
                "disposedOn": "2019/11/16T12:00:00"
            }
        ],
        "id": "615b9335-f78f-4a53-9e33-74e1b601d8bd",
        "message": [
            {
                "culture": "en-US",
                "key": "Title",
                "value": "Controller notification!"
            },
            {
                "culture": "en-US",
                "key": "Message",
                "value": "This is an info message received from PushNotifications controller!"
            }
        ],
        "target": "administrators",
        "ttl": 86400,
        "_rid": "iNZhANsdVb0XAQAAAAAAAA==",
        "_self": "dbs/iNZhAA==/colls/iNZhANsdVb0=/docs/iNZhANsdVb0XAQAAAAAAAA==/",
        "_etag": "\"0500558f-0000-0d00-0000-5dfa08d00000\"",
        "_attachments": "attachments/",
        "_ts": 1576667344
    },
    {
        "createdOn": "2019-12-18T09:48:30.4778759Z",
        "disposed": [
            {
                "disposedBy": "admin@mail.com",
                "disposedOn": "2019/11/16T12:00:00"
            },
            {
                "disposedBy": "anotherAdmin@mail.com",
                "disposedOn": "2019/11/16T12:05:03"
            }
        ],
        "id": "287d1ce0-017e-40b5-ab47-9e2222448b33",
        "message": [
            {
                "culture": "en-US",
                "key": "Title",
                "value": "Controller notification!"
            },
            {
                "culture": "en-US",
                "key": "Message",
                "value": "This is an info message received from PushNotifications controller!"
            }
        ],
        "target": "administrators",
        "ttl": 86400,
        "_rid": "iNZhANsdVb0YAQAAAAAAAA==",
        "_self": "dbs/iNZhAA==/colls/iNZhANsdVb0=/docs/iNZhANsdVb0YAQAAAAAAAA==/",
        "_etag": "\"050092b6-0000-0d00-0000-5dfa0c750000\"",
        "_attachments": "attachments/",
        "_ts": 1576668277
    }
]

现在，我想创建一个查询来获取所有通知，这些通知"target"是由.administratorsadmin@mail.com

我尝试了一些查询，例如：

SELECT value notifications 
FROM notifications JOIN
(SELECT value disposed.disposedBy 
    FROM disposed in notifications.disposed 
    WHERE disposed.disposedBy != "admin@mail.com") 
WHERE notifications.target = "administrators"

和

SELECT value notifications 
FROM notifications JOIN 
(SELECT value disposed.disposedBy 
    FROM disposed IN notifications.disposed 
    WHERE disposed.disposedBy != "admin@mail.com") 
    JOIN (SELECT value notifications 
    WHERE notifications.target = "administrators")

乃至

SELECT value notifications
FROM notifications JOIN 
(SELECT value disposed.disposedBy 
    FROM disposed IN notifications.disposed
    WHERE ARRAY_CONTAINS(disposed.disposedBy, "admin@mail.com"))

但他们都没有给我预期的结果，即只收到一个通知（ID：615b9335-f78f-4a53-9e33-74e1b601d8bd）。

如何构建此查询以获取针对某些特定目标且未由特定用户处置的通知？

编辑1：我已经想出了如果我将处置更改为字符串数组（例如disposed: ["user@mail.com", "anotherUser@mail.com"]使用查询）如何执行此操作

SELECT value notifications 
FROM notifications 
WHERE NOT Array_Contains(notifications.disposed, "user@mail.com")
AND notifications.target = "administrators"

标签： sqlazure-cosmosdbazure-cosmosdb-sqlapi

sql - 用于获取尚未处理的通知的 SQL 查询

问题描述

解决方案

推荐阅读