Create a consistent week object using CosmosDB SQL API

Question

I am trying to create a consistent week object showing results for the last 7 days as output from CosmosDB, the problem is that I need the output JSON to be consistent and show 0 if there are no data but I cannot figure out who.

My query so far looks like this

SELECT COUNT(1) AS RedeemedCount, CONCAT(ToString(DateTimePart("yy", c.RedeemedDate)), ToString(DateTimePart("mm", c.RedeemedDate)), ToString(DateTimePart("dd", c.RedeemedDate))) AS RedeemedDate
FROM c 
JOIN bi IN c.ItemsInBasket
WHERE c.id != null --'e41c8ba6-60cb-48d1-80f6-3f0f2638bd7d'
AND c.Brand = 'foo'
AND bi.Item.PromotionName != null
AND c.RedeemedDate != null
AND c.RedeemedDate >= DateTimeAdd("DD", -700, (SELECT VALUE GetCurrentDateTime()))
AND bi.Item.PromotionName = 'PromOnePiece_20201029_202001_25'
GROUP BY CONCAT(ToString(DateTimePart("yy", c.RedeemedDate)), ToString(DateTimePart("mm", c.RedeemedDate)), ToString(DateTimePart("dd", c.RedeemedDate)))

This Correctly outputs

[
    {
        "RedeemedCount": 1,
        "RedeemedDate": "2020112"
    }
]

But I need it to output like this

[
        {
            "RedeemedCount": 1,
            "RedeemedDate": "2020112"
        },
        {
            "RedeemedCount": 0,
            "RedeemedDate": "2020113"
        },
        ..... up to the last 7 days so in this case 2020112 - 2020118
    ]

Problem is I do not know how I can make this consistent.

Please note: the -700 I know means 700 days its just to get some data for this query it will be -7 when we run this for real

Answer 1

等待大神答复