如何“合并"或“转换" Azure Cosmos DB中的JSON文档 [英] How to "merge" or "transform" JSON documents in Azure Cosmos DB

问题描述

我正在使用Microsoft Bot Framework和Azure设置聊天机器人.我想将"UserState"保存在数据库中，以便轻松分析用户数据.我设法将我的userState以JSON文档的形式保存在Azure Cosmos DB中.

问题是与机器人的每次交互都在Cosmos DB的集合"中创建了一个新的文档".

我如何轻松地合并数据(数据结构是一致的)，并且在最佳情况下将数据存储在某种表中?我要用于分析的工具需要.txt或.csv文件.

这是存储用户数据的JSON文件的代码段.

{
    "id": "emulator*2fusers*2f9321b527-4699-4b4a-8d9d-9cd9fa8f1967*2f",
    "realId": "emulator/users/9321b527-4699-4b4a-8d9d-9cd9fa8f1967/",
    "document": {
        "userData": {
            "name": "value",
            "age": 18,
            "gender": "value",
            "education": "value",
            "major": "value"
        },
        "userDataExtended": {
            "roundCounter": 3,
            "choices": [
                "A",
                "A",
                "B"
            ],
        },
    "_rid": "0k5YAPBrVaknAAAAAAAAAA==",
    "_self": "dbs/0k5YAA==/colls/0k5YAPBrVak=/docs/0k5YAPBrVaknAAAAAAAAAA==/",
    "_etag": "\"ac009377-0000-0000-0000-5c59c5610000\"",
    "_attachments": "attachments/",
    "_ts": 1549387105
}

在最佳情况下，我想将数据包含在具有名称"，年龄"等列以及每个用户(文档)为一行的表结构中.

谢谢！

解决方案

您的问题中有几件事，我将分别解决.

扩展Drew的评论:

您正在创建多个文档，因为您正在通过模拟器运行机器人.模拟器每次重新启动时，都会创建一个新的用户ID，从而为该用户创建一个新文档，并为该用户的对话创建一个新文档.如果您使用仿真器以外的其他渠道，那么只要用户ID保持一致，就不会出现此问题.

关于合并文档:

我不确定您要查找的是什么，但是您可能可以使用SELECT * FROM c会将所有文档合并到一个输出中.

关于文本/csv文件:

我不确定您的工具是什么，但是如果它可以处理JSON，则上面的方法可能对您有用.如果没有，则可以实现自定义中间件来获取所需的txt/csv输出. 这里是一个示例，显示了相对相似的内容. C#中没有等效的示例，但是您仍然可以在V3机器人中受支持，但在V4中被blob存储所取代.您可以在V3中编写您的机器人.与Jay所说的类似，您也许仍然可以使用触发函数将其发送到表存储，但是随后您将数据存储了两次.

关于分析

如果您真正想要的只是分析，请

The problem is that each interaction with the bot creates a new "document" in a "collection" in Cosmos DB.

How can I easily merge the data (data structure is consistent) and in the best case have the data in some kind of table? The tool I want to use for analyzing requires .txt or .csv files.

This is a snippet of the JSON file which stores the user data.

{
    "id": "emulator*2fusers*2f9321b527-4699-4b4a-8d9d-9cd9fa8f1967*2f",
    "realId": "emulator/users/9321b527-4699-4b4a-8d9d-9cd9fa8f1967/",
    "document": {
        "userData": {
            "name": "value",
            "age": 18,
            "gender": "value",
            "education": "value",
            "major": "value"
        },
        "userDataExtended": {
            "roundCounter": 3,
            "choices": [
                "A",
                "A",
                "B"
            ],
        },
    "_rid": "0k5YAPBrVaknAAAAAAAAAA==",
    "_self": "dbs/0k5YAA==/colls/0k5YAPBrVak=/docs/0k5YAPBrVaknAAAAAAAAAA==/",
    "_etag": "\"ac009377-0000-0000-0000-5c59c5610000\"",
    "_attachments": "attachments/",
    "_ts": 1549387105
}

In the best case I want to have the data in a table structure with columns "name", "age", etc. and each user (document) as a row.

Thank you!

解决方案

There's a few things in your questions and I'll address them all separately.

Expanding on Drew's comment:

You have multiple documents being created because you're running the bot through emulator. Each time emulator restarts, it creates a new User ID and therefore a new document for the user and also one for that user's conversation. You will not have this issue if you use a channel other than emulator, provided that the User ID remains consistent.

Regarding merging documents:

I'm not sure exactly what you're looking for, but you might be able to use SQL Queries to accomplish what you need. Just click "New SQL Query". For example, running SELECT * FROM c merges all of the documents into a single output.

Regarding text/csv files:

I'm not sure what your tool is, but if it can handle JSON, then the above might work for you. If not, you can implement custom middleware to get the txt/csv output you're looking for. Here's a sample that shows something relatively similar. There isn't an equivalent example in C#, but you can still implement your own middleware to do the same thing.

Regarding Tables:

If you're really looking for Table Storage, it was supported in V3 bots, but replaced by blob storage in V4. You could write your bot in V3. Similar to what Jay said, you might still be able to use a trigger function to send it to table storage, but then you're storing the data twice.

Regarding Analysis

If all you're really looking for is analysis, Application Insights/Bot Analytics may be what you need, although I don't believe it will provide the detail you're looking for.

这篇关于如何“合并"或“转换" Azure Cosmos DB中的JSON文档的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！