Query into specific format

I have collection in Azure CosmosDB NoSQL, the documents look like this:

{
    "Name": "A1",
    "Tag": "Extra",
    "Daily_data": {
        "2010-01-01": {
            "Value1": {
                "P1": 200,
                "P2": 100,
                "P3": 250,
                "P4": 110
            },
            "Value2": {
                "P1": 200,
                "P2": 100,
                "P3": 250,
                "P4": 110
            }
        },
        "2010-01-02": {
         "Value1": {
                "P1": 200,
                "P2": 100,
                "P3": 250,
                "P4": 110
            },
        "Value2": {
                "P1": 200,
                "P2": 100,
                "P3": 250,
                "P4": 110
            }
        }
    }
}

I am trying to create SQL query to get the data in this format for each document:

{
    "Name": "A1",
    "Tag": "Extra",
    "Daily_data": [
        {
        "Date": "2010-01-01",
        "Values": [
            {
               "Value": "Value1",
                "P1": 200,
                "P2": 100,
                "P3": 250,
                "P4": 110
            },
            {
                "Value": "Value2",
                "P1": 200,
                "P2": 100,
                "P3": 250,
                "P4": 110
            }
        ]
    },
    {
        "Date": "2010-01-02",
        "Values":[
            {
               "Value": "Value1",
               "P1": 200,
               "P2": 100,
               "P3": 250,
               "P4": 110
            },
            {
                "Value": "Value2",
                "P1": 200,
                "P2": 100,
                "P3": 250,
                "P4": 110
            }
        ]
    }
    ]
}

So basically I want to change key value pairs into a list of values that has the key as one of the values inside.

I tried getting list of keys first using the following code but it returns empty string. Any idea how to create this query?

SELECT 
    c.Name,
    c.Tag,
    ARRAY(
        SELECT {
            "Date": item.Key
        }
        FROM item IN c.Daily_data
    ) AS Daily_data
FROM c

I don't know any tricks how to enumerate JSON property keys with SQLAPI SQL query toolset. Not anything pretty and magical..

The right answer would be to sit down with "Business" and explain them that the storage model must be different if they want reasonable development cost and performance;) As long as "business" will get their model on the edge, they should not care how the data lives underneath. Like David Makogon suggested, moving data from keys to values (and perhaps splitting data to smaller documents) will most likely save you/them from a world of pain in future.

If "Business" is really willing to endlessly pay for uglier/costlier/slower solutions, then you could try..

OPTION 1: User-Defined functions.

Most likely you can transform your model however you like with Javascript. The downside is that developing and executing it will be costly and cumbersome. But it may be the dirty temporary trick for you and turn json object keys to array for you or even transform the whole model. Start here: User-defined functions (UDFs) in Azure Cosmos DB.

OPTION 2: Transform in client app on every use

Just fetch the documents as is and implement the transformation logic in client-side, using C#/JS or whatever client you have. The downside is you have to load full documents to client, even if your logic would filter/transform/aggregate the models further. But, depending on your usage patterns, it may just be enough.. At least it buys you time.

OPTION 3: Transform in client, write to separate DB

If you are really into overengineering things, you could also use change feed and write your transformed models continuously to another container for querying/transforming/aggregating against later. This way you "pay" for transformation only once and get all the read-benefits of using cosmos SQLAPI.

But there will be the hassle of maintaining the change feed process, sync issues (change feed does not pass deletes), etc. Having a shared throughput database could help you avoid the extra cost for the side-container.