时间戳

首页 > 解决方案 > 无法使用 Cosmos DB SQL 检索 JSON 值小节的详细信息

json - 无法使用 Cosmos DB SQL 检索 JSON 值小节的详细信息

问题描述

我有一个我正在尝试使用 Cosmos DB SQL 处理的 JSON 数据字段。

这是JSON

{
    "id": "consultationservice||8acd52b4-f4c3-4124-b1c3-89bc57a25f4f",
    "value": {
        "Id": "8acd52b4-f4c3-4124-b1c3-89bc57a25f4f",
        "timeZone": "Pacific Info",
        "proposedMeetingTimes": [
            "2019-01-06T17:16:40",
            "2019-01-06T17:16:40",
            "2019-01-06T17:16:40"
        ],
        "proposedMeetingTime": "2019-01-06T17:16:40",
        "SomeInnerData": [
            {
                "Id": "25c52709-4ef6-4045-a623-5eca3957a532",
                "Email": [
                    "email@email.com",
                    "email1@email.com",
                    "email2@email.com"
                ],
                "connectionInformation": "The best connection",
                "sentOn": "2019-01-06T17:16:40"
            }
        ]
    },
    "partitionKey": "UserAvailability",
    "_rid": "8BMWALYVDbIHAAAAAAAAAA==",
    "_self": "dbs/8BMWAA==/colls/8BMWALYVDbI=/docs/8BMWALYVDbIHAAAAAAAAAA==/",
    "_etag": "\"00000000-0000-0000-169d-6f5b4f8201d7\"",
    "_attachments": "attachments/",
    "_ts": 1615484346
}

我可以使用这种格式检索 VALUE 部分内的任何数据

SELECT UserAvailability["value"].SomeInnerData
FROM UserAvailability
WHERE UserAvailability["value"].Id = '8acd52b4-f4c3-4124-b1c3-89bc57a25f4f'

或者

SELECT UserAvailability["value"].Email
FROM UserAvailability
WHERE UserAvailability["value"].Id = '8acd52b4-f4c3-4124-b1c3-89bc57a25f4f'

但是，无论我尝试做什么，都无法检索 SomeInnerData（例如 Id、Email、connectionInformation、sentOn）部分中的任何数据。请协助

先感谢您

标签： jsonazure-cosmosdbazure-cosmosdb-sqlapi

解决方案

你SomeInnerData 是一个数组，你不能通过UserAvailability["value"].SomeInnerData.Id.

如果此数组中只有一项，则可以尝试以下操作：

SELECT UserAvailability["value"].SomeInnerData[0].Id,UserAvailability["value"].SomeInnerData[0].Email,UserAvailability["value"].SomeInnerData[0].connectionInformation,UserAvailability["value"].SomeInnerData[0].sentOn
FROM UserAvailability
JOIN SID IN UserAvailability["value"].SomeInnerData
WHERE UserAvailability["value"].Id = '8acd52b4-f4c3-4124-b1c3-89bc57a25f4f'

结果：

[
    {
        "Id": "25c52709-4ef6-4045-a623-5eca3957a532",
        "Email": [
            "email@email.com",
            "email1@email.com",
            "email2@email.com"
        ],
        "connectionInformation": "The best connection",
        "sentOn": "2019-01-06T17:16:40"
    }
]

如果你有很多项目，你应该使用JOIN。加入将导致参与加入的集合的完全叉积。

SELECT UserAvailability["value"].SomeInnerData,SID.Id,SID.Email,SID.connectionInformation,SID.sentOn
FROM UserAvailability
JOIN SID IN UserAvailability["value"].SomeInnerData
WHERE UserAvailability["value"].Id = '8acd52b4-f4c3-4124-b1c3-89bc57a25f4f'

结果：

[
    {
        "SomeInnerData": [
            {
                "Id": "25c52709-4ef6-4045-a623-5eca3957a532",
                "Email": [
                    "email@email.com",
                    "email1@email.com",
                    "email2@email.com"
                ],
                "connectionInformation": "The best connection",
                "sentOn": "2019-01-06T17:16:40"
            }
        ],
        "Id": "25c52709-4ef6-4045-a623-5eca3957a532",
        "Email": [
            "email@email.com",
            "email1@email.com",
            "email2@email.com"
        ],
        "connectionInformation": "The best connection",
        "sentOn": "2019-01-06T17:16:40"
    }
]

推荐阅读