Author Advanced Queries and Objects in Cosmos DB for NoSQL

1. Open an incognito or in-private window, and log in to the Azure portal using the username and password provided in the lab environment.
2. Navigate to the Cosmos DB for NoSQL account already deployed in the lab environment. Navigate to the Data Explorer, and navigate to the only database and container deployed to the account. Under the container, select Items. You should see that there are no items in the container, yet.
3. In a separate browser window, navigate to the URL provided in the Additional Information and Resources section of the lab. Copy the data in the file, and paste it to a new document using any text editor of your choice. Make sure you save the document with a .json extension; this is required by the upload feature you will use, next.
4. Back on the Items tab of the Cosmos DB container, select Upload Item, and upload the file you created with the contents you copied from the link provided.
5. In the top menu, select New SQL Query, and run the default query to confirm the items were inserted into your container.

This exercise is designed to demonstrate to your colleagues that JSON data stored in one form in the database can be readily output in a different form for use by the application executing the query.

Note: If you get stuck along the way, scroll down to the Hints and Tips at the end of this objective before turning to the lab guide or the video.

1. In the query windows of the Data Explorer, using the default query to return all properties of all documents, study and profile the data you just uploaded to the container.

   Tip: This set of documents represents a few of the major celestial objects of our solar system. Take particular note of certain properties that are nested under a parent property, such as physicalMetrics, as well as the the nested objects that describe the natural satellites, or moons, for planets that have natural satellites.

2. Write a query over the data to return exactly these objects, in the form shown below, where each moon is listed with its parent planet and a couple of physical features. Note that these results are unordered, so your query should return the same objects in the same form, but they do not need to be in any particular order.

[
    {
        "moon_name": "The Moon",
        "moon_distance": 384400000,
        "planet_Name": "Earth",
        "planet_CelsiusTemp": 14
    },
    {
        "moon_name": "Deimos",
        "moon_distance": 23460000,
        "planet_Name": "Mars",
        "planet_CelsiusTemp": -63
    },
    {
        "moon_name": "Phobos",
        "moon_distance": 9270000,
        "planet_Name": "Mars",
        "planet_CelsiusTemp": -63
    },
    {
        "moon_name": "Triton",
        "moon_distance": 354800000,
        "planet_Name": "Neptune",
        "planet_CelsiusTemp": -214
    },
    {
        "moon_name": "Nereid",
        "moon_distance": 5513000,
        "planet_Name": "Neptune",
        "planet_CelsiusTemp": -214
    },
    {
        "moon_name": "Naiad",
        "moon_distance": 48227000,
        "planet_Name": "Neptune",
        "planet_CelsiusTemp": -214
    },
    {
        "moon_name": "Thalassa",
        "moon_distance": 50075000,
        "planet_Name": "Neptune",
        "planet_CelsiusTemp": -214
    },
    {
        "moon_name": "Despina",
        "moon_distance": 52526000,
        "planet_Name": "Neptune",
        "planet_CelsiusTemp": -214
    },
    {
        "moon_name": "Galatea",
        "moon_distance": 61953000,
        "planet_Name": "Neptune",
        "planet_CelsiusTemp": -214
    },
    {
        "moon_name": "Larissa",
        "moon_distance": 73548000,
        "planet_Name": "Neptune",
        "planet_CelsiusTemp": -214
    },
    {
        "moon_name": "Proteus",
        "moon_distance": 117647000,
        "planet_Name": "Neptune",
        "planet_CelsiusTemp": -214
    },
    {
        "moon_name": "Halimede",
        "moon_distance": 15728000,
        "planet_Name": "Neptune",
        "planet_CelsiusTemp": -214
    },
    {
        "moon_name": "Psamathe",
        "moon_distance": 46695000,
        "planet_Name": "Neptune",
        "planet_CelsiusTemp": -214
    },
    {
        "moon_name": "Sao",
        "moon_distance": 22422000,
        "planet_Name": "Neptune",
        "planet_CelsiusTemp": -214
    },
    {
        "moon_name": "Laomedeia",
        "moon_distance": 23571000,
        "planet_Name": "Neptune",
        "planet_CelsiusTemp": -214
    },
    {
        "moon_name": "Neso",
        "moon_distance": 48387000,
        "planet_Name": "Neptune",
        "planet_CelsiusTemp": -214
    },
    {
        "moon_name": "S/2004 N 1",
        "moon_distance": 105283000,
        "planet_Name": "Neptune",
        "planet_CelsiusTemp": -214
    }
]

Tips and Hints

• meanSurfaceTempF refers to the surface temperature of the planet in degrees Fahrenheit. You can derive an approximate temperature in degrees Celsius by subtracting 32 from the temperature in Fahrenheit and dividing by 1.8.
• The results specified have rounded the Celsius temperature to the nearest whole number.
• Consider constructing your query iteratively by first writing a query that is filtered to return only the planets specified, then one with the new column for the calculated Celsius temperature, rounded to the nearest whole number, and then the final query that formats the output in the form specified.
• You will need to use a WHERE clause to return only the objects specified, but you do not need an ORDER BY clause. There are multiple ways you could construct the WHERE clause.
• You will need to use a JOIN to return the data in the specified format.
• JOIN in SQL over JSON documents in Cosmos DB for NoSQL does not operate the same way it does in SQL over relational data.

This exercise is designed to demonstrate to your colleagues that the user defined functions (UDFs) they have previously used to define common scalar calculations in relational databases can also be created in Cosmos DB for NoSQL. The primary difference is that a UDF in Cosmos DB is written in JavaScript instead of SQL

Note: If you get stuck along the way, scroll down to the Tips and Hints at the end of this objective before turning to the lab guide or the video.

This objective depends on successful completion of the first objective. Follow these steps to build a UDF in the Azure portal:

Set Up the UDF

1. You should already be in the Data Explorer for the predeployed Cosmos DB container, with the SQL query window open.

   Note: Do not close the query window; you will return to it in this objective.

2. Copy the line of code that calculates the property aliased as planet_CelsiusTemp; you can use this for reference.
3. Use the menu in Data Explorer to create a new UDF.
4. Enter the following Id for your UDF: FtoC. (Use this exact name in order for the lab grading feature to give you credit for completing this objective.)
5. Change the generic function name to be the same as the function ID: FtoC.

Code the UDF

6. Using the calculated property from your SQL query as a guide, create a function that takes a single parameter representing a Fahrenheit temperature value and returns the calculated Celsius value, rounded to the nearest whole number.
7. Save the UDF to register it on the container.

Use the UDF in a Query

Return to the query window from the prior objective, and replace the line of code that calculates the property aliased as planet_CelsiusTemp to one that uses the new UDF, passing in the same Fahrenheit property used in the original calculation.

Note: The prefix and name of your user defined function is case sensitive.

Tips and Hints

• The UI should begin with a simple template for the function, but if it does not, you can start with this:

  function userDefinedFunction(){}
  

• Paste in the calculated property from your SQL query, and comment it out with two forward slashes in front of the text to use as a reference in building your UDF. Then, just remove it before saving the UDF.

• There may be a quirk in the portal UI, where the "Save/Update" feature is disabled after you have edited the UDF. If this happens, just make a minor edit in the Id control, and then edit it back to the original value. This action should enable the button for you to save your work.

• The method for rounding in JavaScript is Math.round.

• To call your UDF in the SQL code, prefix the name of the UDF with udf. — noting that both the prefix and the UDF name are case sensitive.

This exercise is designed to demonstrate to your colleagues that the stored procedures they have previously used to run repeatable, transactional code in relational databases can also be created in Cosmos DB for NoSQL. The primary differences are that a stored procedure in Cosmos DB is written in JavaScript instead of SQL, and the scope of a Cosmos DB stored procedure is limited to operate over only container items with the same partition key value. There may be a quirk in the portal UI, where the "Save/Update" feature is disabled after you have edited the UDF.

Note: If you get stuck along the way, scroll down to the "Tips and Hints" at the end of this objective before turning to the lab guide or the video.

During your demo, you want to show your colleagues how stored procedures can execute in a transaction in order to maintain ACID guarantees, where all operations will either fail together or succeed together. With this in mind, it is best if you build an example that operates over at least two items in the database container, so in our stored procedure, we will update two items by adding a new property to each of them.

Profile the Data

Stored procedures are more commonly created and called in Azure Cosmos DB SDK code, but in this objective, you want to leverage the sample code that is provided when creating a stored procedure in the Azure portal.

1. In the Azure portal, you should already be in the Data Explorer for the predeployed Cosmos DB container, with the SQL query window open, which you can now close or just leave open, if you prefer.
2. In the tree next to the query window, navigate below the container name to Scale and Settings, and then under Settings, note the name of the partition key.
3. Open a new SQL query window, and query on just three properties: the partition key property that you just noted, the name and the objectType, ordered by the partition key property that you just noted.
4. Execute and profile the data in the results. You should notice that two of the documents have the same partition key value. This is common in many JSON models where the data design may have some "child" objects embedded in the main object, and others may stand on their own but share the same partition key with the "parent." This serves as a good example for our transactional stored procedure, so note the shared partition key value of those two items.
5. To test that the new property you are going to add to the two items does not already exist, add one more property to the query, associatedWithHumans, and run the query again. The results should be identical to the first run because that property does not yet exist on any documents in the container.

   Note: Do not close this query window; you will use it again.

Set Up the Stored Procedure

6. Using the menu in the Data Explorer, select New Stored Procedure.
7. Enter the following Id for your storedProcedure: addNewProperty. (Use this exact name in order for the lab grading feature to give you credit for completing this objective.)
8. Change the generic function name to be the same as the stored procedure Id: addNewProperty.
9. Save your work.

Test, Code, and Test the Stored Procedure

Most of the code you will see in the sample stored procedure is there to ensure the stored procedure runs in a transaction, by throwing an error if anything goes wrong. You will need to alter just a small part of the code.

10. Study the code and note that the else block is doing the primary work of the stored procedure: returning the items collected in the query earlier in the code. Use the menu to execute the stored procedure.

11. When prompted, provide the partition key value you noted, earlier, which is the same value assigned to two items. Do not enter anything for a parameter.

12. The Results pane should return the JSON for the two documents with the shared partition key value.

13. Now you'll need to add your code to update the two documents with the associatedWithHumans property. Copy the code below and paste it at the beginning of the else block, above the sample code that is already there.

                for (var x in feed)
                {   var doc = feed[x];
                    doc.associatedWithHumans = "yes";
                    collection.replaceDocument(feed[x]._self,doc);
    
                }
    

14. Save (update) your work, and execute the code.

15. The Results pane should return the updated JSON for the first of the two documents with the shared partition key value.

16. To test the results of the stored procedure in a form that is easier to read, return to the query window where you included the associatedWithHumans property, and run it again. The results should include that added property for the two items that share the same partition key.

Tips and Hints

• There may be a quirk in the portal UI, where the "Save/Update" feature is disabled after you have edited the stored procedure. If this happens, just make a minor edit in the Id control, and then edit it back to the original value. This action should enable the button for you to save your work.
• The container partition key is solarObjectId.
• The two items with the shared partition key are named earth and Sputnik.
• The shared partition key value is 300, which an integer but is treated as a string in the JSON.