Cosmos DB SQL shaping the output

Question

How can I use SQL for Cosmos DB to reshape this structure when querying?

Structure in the database:

{
   "id": "id-1",
   "Costs": {
            "Items": {
                "d1": {
                    "Title": "A1",
                    "Amount": 100
                },
                "d2": {
                    "Title": "A2",
                    "Amount": 200
                },
                "d3": {
                    "Title": "A3",
                    "Amount": 300
                }
   }
}

Output-structure:

{
  "id": "id-1",
  "Costs": {
                "d1": 100,
                "d2": 200,
                "d3": 300
  }
}

Answer 1

Here is a way using UDF:

function reshapeItems(items) {
    var result = {};
    for (var key in items) {
        if (items.hasOwnProperty(key)) {
            result[key] = items[key].Amount;
        }
    }
    return result;
}

The SQL will be like this:

SELECT 
    c.id,
    UDF.reshapItems(c.Costs.Items) AS Costs
FROM c
WHERE c.id = "id-1"