CODEWITHSUNDEEP
sql-serverazureazure-cosmosdbcalculated-columns
Robert
Robert

Reputation: 2701

Computed column in Cosmos DB, column base on another columns

In SQL Server we can create computed column. For example:

CREATE TABLE Salary
(
  id INT,
  hours INT,
  medical  INT,
  stake double,
  total AS (hours * stake + medical) persisted
)

How can I create column calculated on another columns in Cosmos DB?

Upvotes: 1

Views: 1619

Answers (3)

jcocchi
jcocchi

Reputation: 11

Azure Cosmos DB now supports computed properties which are similar to SQL computed columns. These properties have values based on existing item properties without being persisted in the actual item itself.

An example definition based on your example would look something like this:

{
    "name": "total",
    "query": "SELECT VALUE (c.hours * c.stake + c.medical) FROM c"
}

After the property is defined you can use it in queries like any other property in your items. It's recommended you index the computed property after creating it for the best performance. Learn more here.

Upvotes: 1

David Makogon
David Makogon

Reputation: 71119

So, while Cosmos DB use a SQL query syntax, note that it's not a relational database such as SQL Server. As such, there is no notion of columns; there are properties within documents (and these may vary from document to document.

Things like computed columns don't exist directly; this is something you'd need to implement yourself. Lots of ways to do this, but if you're looking just at options Cosmos DB provides, you may be able to accomplish what you want with a Cosmos DB pre-trigger, where you can modify a document before it's written (in your case, computing a total property in terms of hours, stake, and medical).

You can find more information on triggers here.

Upvotes: 1

Gaurav Mantri
Gaurav Mantri

Reputation: 136336

AFAIK, computed columns are not available in Cosmos DB. This is something you can do in the code i.e. create the computed column in your code and make it part of the document while saving.

Other option would be to compute this value in your query and return as part of the result.

Upvotes: 0

Related Questions