The series so far:
- Introduction to SQL for Cosmos DB
- SQL For Cosmos DB – Tips and Tricks
- SQL For Cosmos DB – Handling Complex JSON Structures
JSON allows for nested nodes, arrays and arrays of objects, and Cosmos DB SQL can handle all of these when reshaping the output data. Consequently, you will use a second collection when learning how to query more complex JSON documents
This means creating a new collection called complexcars in the Cosmos DB emulator—or even in Cosmos DB if you prefer. Once this collection has been created you need to load into it the documents named complex1.json, complex2.json and complex3.json. Be sure to review how to add the files from this article if you need help.
Each document in this collection looks something like this:
{
"InvoiceNumber": "GBPGB011",
"TotalSalePrice": 89000,
"SaleDate": "2015-04-30T00:00:00",
"Customer": {
"Name": "Wonderland Wheels",
"CreditRisk": false,
"Reseller": true
},
"Address": {
"Town": "London",
"PostCode": "E7 4BR",
"CountryName": "United Kingdom",
"CountryISO": "GBR "
} ,
"CustomerComments": [
"Excellent",
"Wonderful",
"Superb"
],
"Salesdetails": [
{
"LineItem": 1,
"Make": "Porsche",
"Model": "944",
"SellingPrice": 8500,
"LineItemDiscount": 50,
"PurchaseCost": 6800,
"RepairsCost": 250,
"PartsCost": 225,
"TransportInCost": 150
},
{
"LineItem": 2,
"Make": "Bentley",
"Model": "Flying Spur",
"SellingPrice": 80500,
"LineItemDiscount": 500,
"PurchaseCost": 64400,
"RepairsCost": 500,
"PartsCost": 750,
"TransportInCost": 750
}
],
"id": "f58a70dc-f107-d3ba-acda-02f39893eb44",
"_rid": "molfALBK0z8BAAAAAAAAAA==",
"_self": "dbs/molfAA==/colls/molfALBK0z8=/docs/molfALBK0z8BAAAAAAAAAA==/",
"_etag": "\"00000000-0000-0000-c2fa-09169cb001d4\"",
"_attachments": "attachments/",
"_ts": 1549993225
}
In this document, Customer and Address are subnodes of distinct objects inside the document. Salesdetails is an array of objects and CustomerComments is an array of multiple items.
Choose Attributes from Subnodes
Returning the contents of a node in a JSON collection is mercifully simple. All you have to do is to specify the node, like this:
SELECT c.Address FROM c
The output is the selected node-exactly as it appears in the source document:
[
{
"Address": {
"Town": "London",
"PostCode": "E7 4BR",
"CountryName": "United Kingdom",
"CountryISO": "GBR "
}
},
{
"Address": {
"Town": "Liverpool",
"PostCode": "LL1 001",
"CountryName": "United Kingdom",
"CountryISO": "GBR "
}
},
{
"Address": {
"Town": "London",
"PostCode": "NW1 1AA",
"CountryName": "United Kingdom",
"CountryISO": "GBR "
}
}
]
In practice, this approach can be a useful way of returning multiple elements.
Of course, you can make the output even more fine-grained and return selected attributes from a specific node:
SELECT c.Customer.Name
,c.Customer.CreditRisk
FROM c
The result in this case is as simple as it is predictable:
[
{
"Name": "Wonderland Wheels",
"CreditRisk": false
},
{
"Name": "Honest John",
"CreditRisk": false
},
{
"Name": "Cut and Shut",
"CreditRisk": true
}
]
And it goes without saying that you can mix and match JSON attributes from varying levels in the hierarchy of nodes by running queries like this one:
SELECT c.TotalSalePrice AS InvoiceAmount
,c.Customer.Name
,c.Customer.CreditRisk
,c.Address.Town
FROM c
In this case the result is:
[
{
"InvoiceAmount": 89000,
"Name": "Wonderland Wheels",
"CreditRisk": false,
"Town": "London"
},
{
"InvoiceAmount": 95000,
"Name": "Honest John",
"CreditRisk": false,
"Town": "Liverpool"
},
{
"InvoiceAmount": 170000,
"Name": "Cut and Shut",
"CreditRisk": true,
"Town": "London"
}
]
While on this subject it is worth noting that:
- You can mix from different levels.
- You can drill down to any node merely by specifying the exact path down through the hierarchy of nodes.
- You can return the contents of an entire node as well as selected attributes from other nodes using queries like this:
SELECT c.TotalSalePrice AS InvoiceAmount
,c.Customer.Name
,c.Customer.CreditRisk
,c.Address
FROM c
This query gives the following output:
[
{
"InvoiceAmount": 89000,
"Name": "Wonderland Wheels",
"CreditRisk": false,
"Address": {
"Town": "London",
"PostCode": "E7 4BR",
"CountryName": "United Kingdom",
"CountryISO": "GBR "
}
},
{
"InvoiceAmount": 95000,
"Name": "Honest John",
"CreditRisk": false,
"Address": {
"Town": "Liverpool",
"PostCode": "LL1 001",
"CountryName": "United Kingdom",
"CountryISO": "GBR "
}
},
{
"InvoiceAmount": 170000,
"Name": "Cut and Shut",
"CreditRisk": true,
"Address": {
"Town": "London",
"PostCode": "NW1 1AA",
"CountryName": "United Kingdom",
"CountryISO": "GBR "
}
}
]
You may remember from the previous article that you can use the ROOT keyword to indicate the collection. Well this is also possible when querying subnodes, like this:
SELECT * FROM ROOT.Customer
Choose Elements from an Array of Objects
If a JSON document contains arrays of objects (as is the case for salesdetails in the sample documents) then you might need to extend the SQL slightly depending on how you want to display the data. Essentially you have a couple of possibilities:
- Return the entire arrays of objects, that is, everything inside the array
- Specify the item in the object that you wish to output
- Flatten the output and return selected items from each object
Let’s look at each of these approaches in turn using the Salesdetails object in the sample document.
Returning the complete contents of the object is an extension of the technique that you saw previously when returning the complete contents of a node:
SELECT * FROM c.Salesdetails
Executing this query produces the following result:
[
[
{
"LineItem": 1,
"Make": "Porsche",
"Model": "944",
"SellingPrice": 8500,
"LineItemDiscount": "50",
"PurchaseCost": 6800,
"RepairsCost": 250,
"PartsCost": 225,
"TransportInCost": 150
},
{
"LineItem": 2,
"Make": "Bentley",
"Model": "Flying Spur",
"SellingPrice": 80500,
"LineItemDiscount": 500,
"PurchaseCost": 64400,
"RepairsCost": 500,
"PartsCost": 750,
"TransportInCost": 750
}
]
]
The output is truncated in this example, as all six line items from the source documents are returned by the query. However, once you see a couple of them the principle. Hopefully, is clear.
However, the structure can be simplified and returned as a less complex and deep array if you use the IN keyword, like this:
SELECT * FROM l IN c.Salesdetails
Here the result is subtly different:
[
{
"LineItem": 1,
"Make": "Porsche",
"Model": "944",
"SellingPrice": 8500,
"LineItemDiscount": "50",
"PurchaseCost": 6800,
"RepairsCost": 250,
"PartsCost": 225,
"TransportInCost": 150
},
{
"LineItem": 2,
"Make": "Bentley",
"Model": "Flying Spur",
"SellingPrice": 80500,
"LineItemDiscount": 500,
"PurchaseCost": 64400,
"RepairsCost": 500,
"PartsCost": 750,
"TransportInCost": 750
}
]
Once again, only a subset of the output data is displayed here.
If you don’t want the entire contents of the object you can tweak the SELECT statement to isolate only the required attributes from the array.
SELECT l.Make FROM l IN c.Salesdetails
Here, as you can see, you are returning only a subset of the items in each array:
[
{
"Make": "Porsche"
},
{
"Make": "Bentley"
},
{
"Make": "Aston Martin"
},
{
"Make": "Rolls Royce"
},
{
"Make": "Porsche"
},
{
"Make": "Jaguar"
}
]
What is interesting to note here is that you are using one alias (c) to refer to the collection and another alias (l) to refer to the object itself.
Moreover, you can count the number of objects in an object with an extension of the code you saw above. Here I am using the VALUE keyword to return the value without a JSON attribute name.
SELECT VALUE COUNT(l) FROM l IN c.Salesdetails
The result is simply:
[
6
]
Naturally, you can mix attributes from inside the object with attributes from elsewhere in the document:
SELECT c.InvoiceNumber
,c.Customer.Name
,c.Salesdetails
FROM c
In this case the result is:
[
{
"InvoiceNumber": "GBPGB011",
"Name": "Wonderland Wheels",
"Salesdetails": [
{
"LineItem": 1,
"Make": "Porsche",
"Model": "944",
"SellingPrice": 8500,
"LineItemDiscount": "50",
"PurchaseCost": 6800,
"RepairsCost": 250,
"PartsCost": 225,
"TransportInCost": 150
},
{
"LineItem": 2,
"Make": "Bentley",
"Model": "Flying Spur",
"SellingPrice": 80500,
"LineItemDiscount": 500,
"PurchaseCost": 64400,
"RepairsCost": 500,
"PartsCost": 750,
"TransportInCost": 750
}
]
},
{
"InvoiceNumber": "GBPGB001",
"Name": "Honest John",
"Salesdetails": [
{
"LineItem": 1,
"Make": "Aston Martin",
"Model": "DB10",
"SellingPrice": 185000,
"LineItemDiscount": "5000",
"PurchaseCost": 125000,
"RepairsCost": 2500,
"PartsCost": 2025,
"TransportInCost": 150
},
{
"LineItem": 2,
"Make": "Rolls Royce",
"Model": "Silver Ghost",
"SellingPrice": 82500,
"LineItemDiscount": 500,
"PurchaseCost": 54500,
"RepairsCost": 500,
"PartsCost": 750,
"TransportInCost": 750
}
]
},
{
"InvoiceNumber": "GBPGB002",
"Name": "Cut and Shut",
"Salesdetails": [
{
"LineItem": 1,
"Make": "Porsche",
"Model": "924",
"SellingPrice": 95000,
"LineItemDiscount": "5000",
"PurchaseCost": 48000,
"RepairsCost": 250,
"PartsCost": 225,
"TransportInCost": 150
},
{
"LineItem": 2,
"Make": "Jaguar",
"Model": "XK",
"SellingPrice": 65000,
"LineItemDiscount": 500,
"PurchaseCost": 60000,
"RepairsCost": 500,
"PartsCost": 750,
"TransportInCost": 750
}
]
}
]
Specifying the path to the arrays of objects is enough to return the entire contents of the array of objects.
Specifying the individual item inside an array of objects means tweaking the SQL and indicating the (zero-based) item that you want to see in the output, like this:
SELECT c.InvoiceNumber
,c.Customer.Name
,c.Salesdetails[0].Make
,c.Salesdetails[0].Model
FROM c
As you can see below, on this occasion you are only returning one object from the array:
[
{
"InvoiceNumber": "GBPGB011",
"Name": "Wonderland Wheels",
"Make": "Porsche",
"Model": "944"
},
{
"InvoiceNumber": "GBPGB001",
"Name": "Honest John",
"Make": "Aston Martin",
"Model": "DB10"
},
{
"InvoiceNumber": "GBPGB002",
"Name": "Cut and Shut",
"Make": "Porsche",
"Model": "924"
}
]
Conversely, flattening the output to return all the items in an array involves using the JOIN keyword, and joining the document to itself-or more precisely to the array itself. If it helps, you can consider this as nearly equivalent to a table join in T-SQL only the second table is an arrays of objects inside the JSON document itself.
SELECT c.InvoiceNumber
,c.Customer.Name
,cx.LineItem
,cx.Make
,cx.Model
,cx.SellingPrice
FROM c
JOIN cx IN c.Salesdetails
Here the output structure is decidedly different:
[
{
"InvoiceNumber": "GBPGB011",
"Name": "Wonderland Wheels",
"LineItem": 1,
"Make": "Porsche",
"Model": "944",
"SellingPrice": 8500
},
{
"InvoiceNumber": "GBPGB011",
"Name": "Wonderland Wheels",
"LineItem": 2,
"Make": "Bentley",
"Model": "Flying Spur",
"SellingPrice": 80500
},
{
"InvoiceNumber": "GBPGB001",
"Name": "Honest John",
"LineItem": 1,
"Make": "Aston Martin",
"Model": "DB10",
"SellingPrice": 185000
},
{
"InvoiceNumber": "GBPGB001",
"Name": "Honest John",
"LineItem": 2,
"Make": "Rolls Royce",
"Model": "Silver Ghost",
"SellingPrice": 82500
},
{
"InvoiceNumber": "GBPGB002",
"Name": "Cut and Shut",
"LineItem": 1,
"Make": "Porsche",
"Model": "924",
"SellingPrice": 95000
},
{
"InvoiceNumber": "GBPGB002",
"Name": "Cut and Shut",
"LineItem": 2,
"Make": "Jaguar",
"Model": "XK",
"SellingPrice": 65000
}
]
What is important here is to alias the arrays of objects as the focus of the JOIN keyword and use the IN keyword to identify the path to the arrays of objects in the document. If your document contains multiple arrays of objects that you wish to flatten, then you simply add further JOIN clauses.
Put another way, FROM defines the collection, and JOIN refers to the “inner document” (the array of objects) contained in the outer JSON document.
Handling Arrays
JSON also uses arrays to store items inside a document. The ComplexCars document contains an array named CustomerComments. You can return the contents of an array much like you output the contents of an object using the IN keyword.
SELECT * FROM l IN c.CustomerComments
This query returns the entire contents of the array-from all the documents:
[
"Excellent",
"Wonderful",
"Superb",
"Brilliant",
"Magnificent",
"Amazing"
]
Searching Inside an Array
Complex JSON documents can contain arrays of elements, and it is always possible that you may need to search inside an array for a specific item. Cosmos DB SQL lets you use the ARRAY_CONTAINS() function to handle this particular challenge:
SELECT c.InvoiceNumber
,c.Customer.Name
FROM c
WHERE ARRAY_CONTAINS(c.CustomerComments, "Superb")
This time only elements from a document where the array contains the specified text is returned:
[
{
"InvoiceNumber": "GBPGB011",
"Name": "Wonderland Wheels"
}
]
Handling Schema on Read
As you may have already discovered (or doubtless soon will) one of the difficulties in a schema-free approach to storing data is that attributes not only do not always appear in JSON documents, but that the same attribute can have different names across the documents in a collection.
It follows that you will need to handle alternative attribute names in JSON documents. The classic way to prevent the lack of a schema causing erroneous output is to use the coalesce (double question mark) operator-like this:
SELECT s.Address.Town ?? s. Address.City AS TownOrCity FROM s
The output is as simple as the query:
[
{
"TownOrCity": "London"
},
{
"TownOrCity": "Liverpool"
},
{
"TownOrCity": "London"
}
]
The coalesce operator is simple: if the first attribute is nonexistent, then the second one is used. As you can imagine, this operator can save you considerable grief through minimizing erroneous output. However, a few comments may help even further:
If you do not add an alias – $1, $2 etc. is used
You can extend coalesce operator to handle multiple attribute names by writing code like this:
SELECT s.Address.Town ?? s.Address.City ?? s.Address.Village
AS TownOrCity
FROM s
You should look for empty braces in the output that indicate an unhandled attribute.
The challenge in these cases is, of course, discovering the duplicate attributes. This is explained a little further down in this article.
Dealing with Schema on Read in WHERE clause
Multiple attribute names for the same attribute does not only cause issues in the SELECT clause. You may need to filter on an attribute that has multiple synonyms in the document structures. Fortunately, this is not difficult to deal with-as the following SQL shows. Run this code against the simplecars2 collection.
SELECT s.InvoiceNumber,
s.Town ?? s.City ?? s.Village AS TownOrCity
FROM s
WHERE s.Town = "Birmingham"
OR s.City = "Birmingham"
OR s.Village = "Birmingham"
Running this query produces the following JSON:
[
{
"InvoiceNumber": "GBPGB001",
"TownOrCity": "Birmingham"
},
{
"InvoiceNumber": "GBPGB003",
"TownOrCity": "Birmingham"
},
{
"InvoiceNumber": "GBPGB002",
"TownOrCity": "Birmingham"
}
]
Avoid Missing Elements and Ensure a Complete Structure
Another variation on a theme of schema fluidity is the occasional need to ensure that the output JSON has a rigid and predictable format. Here again the coalesce operator can be used to guarantee that an attribute will appear in the result set even if it is missing from the source document. Note that in this query, I am using the simplecars2 collection that you saw in the previous article.
SELECT simplecars.InvoiceNumber
,simplecars.TotalSalePrice
,simplecars.Town ?? "N/A" AS Metropolis
FROM simplecars
This query will return seven records, but here are two examples:
[
{
"InvoiceNumber": "GBPGB001",
"TotalSalePrice": 65000,
"Metropolis": "Birmingham"
},
{
"InvoiceNumber": "GBPGB003",
"TotalSalePrice": 19500,
"Metropolis": "N/A"
},
…
]
Checking Document Structure and Data Types
Cosmos DB SQL comes with a handful of functions that can assist in minimizing the risk of error that is implicit in the free-form structure of JSON.
Replace strings with 0
Unlike T-SQL, Cosmos DB SQL will not attempt to convert numeric text values to numbers. The direct consequence of this is that any attribute where the value is defined as a string cannot be used in a calculation as this will prevent the calculation from working (and the calculated attribute will not appear in the output). If you look at the single JSON document in the complexcars collection you will see that the first value for the LineItemDiscount attribute is “50” (in double quotes). This will prevent the attribute from being used in a calculation and will prevent any value from being returned.
One solution is to use the IS_NUMBER() function (with a little ternary logic) to replace numeric strings with zeroes, like this:
SELECT c.InvoiceNumber
,IS_NUMBER(c.Salesdetails[0].LineItemDiscount)
? c. Salesdetails[0].LineItemDiscount : 0
AS LineItemDiscount
FROM c
This query works beautifully, and gives the following result:
[
{
"InvoiceNumber": "GBPGB011",
"LineItemDiscount": 0
},
{
"InvoiceNumber": "GBPGB001",
"LineItemDiscount": 0
},
{
"InvoiceNumber": "GBPGB002",
"LineItemDiscount": 0
}
]
Clearly a conversion function would be ideal. Unfortunately, this is not directly available in Cosmos DB SQL. However, there is an alternative which is to write your own type casting function in JavaScript and use this instead. You can, of course, extend this technique to list all values that are stored as strings and therefore could mean that erroneous results are returned. The code for this is extremely simple:
SELECT c.InvoiceNumber FROM c WHERE IS_NUMBER(c.LineItemDiscount) = false
Detect Inappropriate Data Types
Equally, there could be occasions when you need to detect a string data type. This is a simple call to the IS_STRING() function.
SELECT c.InvoiceNumber FROM c WHERE IS_STRING(c.Salesdetails[0].LineItemDiscount)
The query output tells you that at least one invoice has a string where you would expect a numeric value:
[
{
"InvoiceNumber": "GBPGB011"
},
{
"InvoiceNumber": "GBPGB001"
},
{
"InvoiceNumber": "GBPGB002"
}
]
The remaining type detection functions that you may find useful are:
IS_BOOL
IS_ARRAY
IS_OBJECT
IS_PRIMITIVE (these can be string, boolean, numeric or null)
Shape Output JSON
Earlier in this article you learned how to “flatten” JSON. Inversely, there could be times when you need to create a different JSON structure for the output. Suppose, for example, you wish to take the flattened documents from the simplecars2 collection and display them in a nested format.
The following short piece of SQL shows how this can be done. Please note that I am deliberately not attempting to output all the attributes from the documents.
SELECT c.InvoiceNumber , {"DateOfSale": c.SaleDate
,"SellingPrice": c.TotalSalePrice} AS InvoiceDetails
FROM c
Here is the result:
[
{
"InvoiceNumber": "GBPGB011",
"InvoiceDetails": {
"DateOfSale": "2015-04-30T00:00:00",
"SellingPrice": 89000
}
},
{
"InvoiceNumber": "GBPGB001",
"InvoiceDetails": {
"DateOfSale": "2017-07-10T00:00:00",
"SellingPrice": 95000
}
},
{
"InvoiceNumber": "GBPGB002",
"InvoiceDetails": {
"DateOfSale": "2018-09-30T00:00:00",
"SellingPrice": 170000
}
}
]
Another way to obtain exactly the same result is (and I know that it looks a little weird) is:
SELECT c.InvoiceNumber
,(SELECT c. SaleDate, c.TotalSalePrice FROM c)
AS InvoiceDetails
FROM c
Subqueries
As you saw in the previous article, Cosmos DB SQL allows you to use subqueries. One useful application of subqueries is to restructure the JSON in the output of a query. In the following case the subquery “flattens” the JSON structure in the complexcars document format-and the outer query then constructs a totally different document format.
SELECT a.Name
,{"Make": a.Make, "Model": a.Model
, "SellingPrice": a.SellingPrice} AS InvoiceDetails
FROM
(SELECT c.InvoiceNumber
,c.Customer.Name
,cx.LineItem
,cx.Make
,cx.Model
,cx.SellingPrice
FROM c
JOIN cx IN c.Salesdetails) a
The format this time is completely different, as you have two objects in the output. All in all, this is rather like a standard SQL JOIN.
[
{
"Name": "Wonderland Wheels",
"InvoiceDetails": {
"Make": "Porsche",
"Model": "944",
"SellingPrice": 8500
}
},
{
"Name": "Wonderland Wheels",
"InvoiceDetails": {
"Make": "Bentley",
"Model": "Flying Spur",
"SellingPrice": 80500
}
},
{
"Name": "Honest John",
"InvoiceDetails": {
"Make": "Aston Martin",
"Model": "DB10",
"SellingPrice": 185000
}
},
{
"Name": "Honest John",
"InvoiceDetails": {
"Make": "Rolls Royce",
"Model": "Silver Ghost",
"SellingPrice": 82500
}
},
{
"Name": "Cut and Shut",
"InvoiceDetails": {
"Make": "Porsche",
"Model": "924",
"SellingPrice": 95000
}
},
{
"Name": "Cut and Shut",
"InvoiceDetails": {
"Make": "Jaguar",
"Model": "XK",
"SellingPrice": 65000
}
}
]
Conclusion
This concludes your whirlwind tour of Cosmos DB SQL. The good news is that you have already seen a large number of the currently available functions, as well as gained a reasonable overview of the core approaches that you may need when querying JSON documents in Cosmos DB. In fact, there are now very few of the Cosmos DB SQL API functions that you have not seen in these two articles.
This very simplicity is, however, a two-edged sword. It is hard to deny that an experienced SQL programmer will feel frustrated at the absence of a range of functions that have been a fundamental part of T-SQL for years-if not decades. Moreover, it has to be said that Cosmos DB SQL – at least in its current incarnation – is not an analytical tool.
However, I prefer to concentrate on the positives, and to point out that once you have mastered Cosmos DB SQL you can use it to export flattened JSON to an SQL Server using a variety of data ingestion techniques and carry out analytics on a reduced data set in the relational or even dimensional engines that you are currently using. Consequently, I encourage you to think of Cosmos DB and its SQL API as an essential extension to the SQL Server universe, and the SQL it offers -however limited-as essentially an easy way in to the worlds of JSON, document databases, NoSQL and big data.
The post SQL For Cosmos DB – Handling Complex JSON Structures appeared first on Simple Talk.
from Simple Talk http://bit.ly/2WG57Yf
via
