Alias of an object key using COSMOSDB sql query

Question

I am working with Cosmos DB and I want to write a SQL query that returns different name of an key in document object.

To elaborate, imagine you have the following document in one container having "makeName" key in "make" object.

{ 
   "vehicleDetailId":"38CBEAF7-5858-4EED-8978-E220D2BA745E",
   "type":"Vehicle",
   "vehicleDetail":{ 
      "make":{ 
         "Id":"B57ADAAD-C16E-44F9-A05B-AAB3BF7068B9",
         "makeName":"BMW"
      }
   }
}

I want to write a query to display "vehicleMake" key in place of "makeName". How to give alias name in the nested object property.

Output should be like below

{ 
   "vehicleDetailId":"38CBEAF7-5858-4EED-8978-E220D2BA745E",
   "type":"Vehicle",
   "vehicleDetail":{ 
      "make":{ 
         "Id":"B57ADAAD-C16E-44F9-A05B-AAB3BF7068B9",
         "vehicleMake":"BMW"
      }
   }
}

I have no idea how to query in Cosmosdb to get the above result.

Answer 1

Try this:

SELECT c.vehicleDetailId, c.type, 
{"make":{"Id":c.vehicleDetail.make.Id, "vehicleMake":c.vehicleDetail.make.makeName}} as vehicleDetail 
FROM c

It uses the aliasing described in the following documentation. All of the aliasing examples I could find in the documentation or blog posts only show a single level of json output, but it happens that you can nest an object (make) within an object (vehichleDetail) to get the behavior you want.

https://docs.microsoft.com/en-us/azure/cosmos-db/sql-query-aliasing

Answer 2

Aliases for properties are similar to the way you'd create a column alias in SQL Server, with the as keyword. In your example, it would be:

SELECT c.vehicleDetail.make.makeName as vehicleMake
FROM c

This would return:

[
  {
    "vehicleMake": "BMW"
  }
]