Challenge 4: Solution

Walkthrough

In this challenge, we can solve the problem using a user-defined function.

UDF

The easiest part of this implementation is to write the actual UDF code. In this example, we will create a udf named getRoomsWithNewRate.

This UDF will iterate over the array of rooms and increase their individual rates:

function getRoomsWithNewRate(rooms){
    rooms.forEach(room =>
        room.monthlyRate = room.monthlyRate * 1.25
    );
    return rooms;
}

Updated Query

You can then refer to this udf using the udf.getRoomsWithNewRate expression in your SQL query.

Unfortunately, our base query is too simple:

SELECT * FROM locations l WHERE l.id = {id}

To make this work, we will need to expand the query so we can reference the monthlyRate property directly:

SELECT
    l.id,
    l.name,
    l.longitude,
    l.latitude,
    l.mailingAddress,
    l.territory,
    l.parkingIncluded,
    l.conferenceRoomsIncluded,
    l.rooms
FROM locations l WHERE l.id = {id}

Now, we can make use of the UDF:

SELECT
    l.id,
    l.name,
    l.longitude,
    l.latitude,
    l.mailingAddress,
    l.territory,
    l.parkingIncluded,
    l.conferenceRoomsIncluded,
    udf.getRoomsWithNewRate(l.rooms)
FROM locations l WHERE l.id = {id}

Deployment Template

Use this template to deploy a pre-baked solution to your Azure subscription: