Virtual columns

Virtual columns in Team Edition let you display calculated values in query results without changing the underlying database schema. They use an expression language based on the Apache Commons JEXL engine, which supports JavaScript-like syntax, functions, and variables. Expressions are evaluated in real time when you view data.

Note

Virtual columns exist only inside Team Edition and are not saved in the database.

How to use

You can access the settings for configuring virtual columns through two different methods:

1. Properties Editor:

   1. Navigate to the Properties Editor.
   2. Select the Virtual tab from the Tabbed editors.
   3. In the Virtual columns section, click Add
   4. Enter a name and an expression for the new column

   

2. Data Editor

   1. Right-click inside the Data Editor
   2. Select Logical structure -> Add virtual column
   3. Enter a name and an expression

   

Info

Virtual columns are shown alongside other columns in the Data Editor. Their values are calculated dynamically based on your expression.

Functions

Functions are grouped into namespaces. You can refer to the functions in the namespaces as variables - nsName.functionName(parameters).

math

You can access all math functions as math.function(parameters).

Info

See the full list of supported math functions in the Oracle documentation.

geo

Function	Parameters	Description
wktPoint	(longitude, latitude)	Produces WKT (geometry) point out of two coordinates. Default SRID is 4326.
wktPoint	(longitude, latitude, srid)	Produces WKT (geometry) point out of two coordinates and SRID

content

If you have JSON or XML columns in your table, you can add a virtual column with an expression for these columns.

Use content.json(<columnName>)[parameter1][parameter2] pattern to create expression for JSON column.

Example

content.json(column1)['glossary']['GlossDiv']['title']

Use content.xml(columnName, "expression") or content.xml(columnName, "returnType", "expression") patterns to create an expression for an XML column. The quotation is important for parsing processes. The "expression" parameter is an XPath query used to select data from the XML.

XML expression can return types: - string - number - boolean - node - nodeset

All these types can be used without quotes in the return type parameter. content.xml(columnName, "expression") returns string by default.

Example

content.xml(column1, "nodeset", "/Employees/Employee[gender='Female']/name/text()")

row

row is a map of all columns in the current row, where keys are column names and values are column values. Use quotes or backticks for names with spaces, punctuation, or digits.

Example

row['First Name'] + ' ' + row['Last Name']

You can also refer to columns without row if their names are simple (no spaces or special characters).

Example

FirstName + ' ' + LastName

table

table contains metadata about the current table. Supports:

• name
• schema
• container

Example

table.name

Editing virtual columns

To modify or delete an existing virtual column:

1. Access the settings for configuring virtual columns.

2. Select the column you want to change.

3. To edit, click Edit and update the name or expression.

4. To delete, click Remove.

Note

Changes apply only in DBeaver and don’t affect the database schema.

Defining virtual keys

In cases where a table lacks a unique key, Team Edition provides the functionality to define a virtual key. This feature allows the creation of a custom set of columns that act as a unique identifier for table rows, facilitating the saving of changes in the absence of traditional unique constraints.

Info

Refer to the Virtual Keys documentation for additional details on configuring and using virtual keys.