How to "merge" or "transform" JSON documents in Azure Cosmos DB

Question

I'm setting up a Chatbot with the Microsoft Bot Framework and Azure. I want to save my "UserState" in a database in order to easily analyze the user data. I managed to save my userState in form of JSON documents in Azure Cosmos DB.

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!

It should not be doing that at all. It should be storing state across turns using a consistent id for the documents that is tied to the identifiers of the channel and conversation or user. Your screenshot does not show enough information. I need to see more than one document. Can you maybe copy/paste a few of the documents into a public Gist so I can look them over? — Drew Marsh

mdrichardson mdrichardson · Accepted Answer · 2019-02-06T19:21:36

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.