Freeing myself from the shackles of standard T-SQL, I’ve been getting to know the Cosmos DB (Was Document DB) No-SQL platform as a service (PaaS) as part of a number of projects. Me being the data guy was asked to create some queries and just add any value I can to the data side of it. So I’ve been finding out the limits and the approach to doing things in to a bunch of stuff with the JSON files.

SELECT VALUE {   "TotalSales":SUM(s.SoldPrice)
             ,   "VenueName":(s.LocationDescription)
             }
FROM sales s
GROUP BY s.LocationDescription

That did not work. Oh, why not. School boy error, I hadn’t read my homework correctly. I knew that Cosmos DB supported aggregations, but not grouping. So the above is not valid. So only the following is valid

SELECT VALUE {"TotalSales":SUM(s.SoldPrice)}
FROM sales s

and so is

SELECT VALUE {   "TotalSales":SUM(s.SoldPrice)
             ,   "NumberOfSales":COUNT(s.SoldPrice)
             }
FROM sales s

SELECT VALUE{s.LineItem}
FROM sales s

SELECT VALUE{p.LineItem}
FROM sales s
JOIN o IN s.order
JOIN c IN o.customer
JOIN p IN c.products