Introducing The Generated Columns Feature

Backendless version 5.7.3 introduces generated columns, a new feature of the Backendless Database. Using this feature, you can declare a new type of column for which the database calculates the values based on a specified expression.

These new columns function much like formulas in your favorite spreadsheet application. When you add a generated column, you provide the formula and the database automatically calculates the results based on the data in your database. There is a very rich “expression language” that supports a variety of functions to work on data.

Learn all about this exciting new feature in the video below, then keep reading for some examples of the feature in action.

Generate Column Examples

To best illustrate this new feature, let’s look at some examples.

Suppose a table has INT columns called op1 and op2. The columns can contain numeric values. The following are some examples of the generated columns:

• A column that calculates the sum of op1 and op2 can be declared like this:

• A column that calculates the product of op1 and op2:

• A column that figures out if the op1 value is negative, zero or positive:

Based on the examples above, here’s what we have when the database gets real values for op1 and op2:

Generate Column Data Retrieval

We call these types of columns “Generated” because the value for these columns is dynamically generated. When the data in the database changes, the value in a generated column changes with it.

However, from the data retrieval perspective, they work exactly the same as any other column. Here’s an example response:

[
	{
		"op2": 5,
		"product": 0,
		"created": 1586296738434,
		"op1Info": "zero",
		"sum": 5,
		"updated": null,
		"objectId": "0464C07C-9D39-E116-FFFD-D7578681F800",
		"op1": 0,
		"ownerId": null,
		"___class": "Math"
	},
	{
		"op2": 43,
		"product": 645,
		"created": 1586295967473,
		"op1Info": "positive",
		"sum": 58,
		"updated": 1586296258320,
		"objectId": "119C28A5-43F8-29EC-FF9A-A9B7C4C10800",
		"op1": 15,
		"ownerId": null,
		"___class": "Math"
	},
	{
		"op2": 2,
		"product": 2,
		"created": 1586295939260,
		"op1Info": "positive",
		"sum": 3,
		"updated": 1586295941344,
		"objectId": "65126BA3-D392-CFA2-FFFF-AEBE878FF600",
		"op1": 1,
		"ownerId": null,
		"___class": "Math"
	},
	{
		"op2": 21,
		"product": -63,
		"created": 1586296731673,
		"op1Info": "negative",
		"sum": 18,
		"updated": null,
		"objectId": "ACE4B1A9-7B0E-701A-FFB7-3ACFCE53CA00",
		"op1": -3,
		"ownerId": null,
		"___class": "Math"
	}
]

You can apply a where clause to these columns and query the database by the values of the columns. For example, to get all the objects where sum > 10, we get:

A Second Example

Let’s look at another example. Here’s a sample schema:

The schema above has a generated column called daysLived. The column is defined as:

Now the database can easily calculate the number of days from birthday till today:

Generated columns are available now in Backendless. For a list and explanation of operations supported in generate column expressions, click here.

That’s all folks! Check out the other new features we introduced in v5.7.3:

• How To Use Smart Text To Personalize Push Notification Images
• How To Include And Exclude Properties From A Response
• How To Use Functions For Property Extraction And Conversion

We can’t wait to see how you put these new features to work. Happy Coding!