Linked Questions

Popular Questions

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

Asked by At

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.

enter image description here

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!

Related Questions