The OBJECTTABLE function processes an object input to produce tabular output. The function itself defines what columns it projects. The OBJECTTABLE function is implicitly a nested table and may be correlated to the preceding FROM clause entries.

Usage

OBJECTTABLE([LANGUAGE lang] rowScript [PASSING val AS name ...] COLUMNS colName colType colScript [ DEFAULT defaultExpr] ...) AS id
SQL

Parameters

ParameterDescription
langOptional string literal that is the case sensitive language name of the scripts to be processed.The script engine must be available via a JSR-223 ScriptEngineManager lookup. In some instances, this may mean making additional modules available to your VDB, which can be done via the same process as adding modules/libraries for UDFs. If a LANGUAGE is not specified, the default value dv_script (see below) will be used
nameIdentifier that will bind the val expression value into the script context
rowScriptString literal specifying the script to create the row values. For each non-null item, the Iterator produces the columns will be evaluated
colName/colType Id/data type of the column, which can optionally be defaulted with the DEFAULT clause expression defaultExpr

colScript

String literal specifying the script that evaluates to the column value

Syntax Rules

  • The columns names must not contain duplicates;
  • The Data Virtuality Server will place several special variables in the script execution context. The CommandContext is available as dv_context. Additionally, the colScripts may access dv_row and dv_row_number. dv_row is the current row object produced by the row script.  dv_row_number is the current 1-based row number;
  • rowScript is evaluated to an Iterator. If the result is already an Iterator, it is used directly. If the evaluation result is an Iteratable, then an Iterator will be obtained. Any other object will be treated as an Iterator of a single item. In all cases, null row values will be skipped.
While there is no restriction on what can be used as a PASSING variable name, you should choose names that can be referenced as identifiers in the target language. 

Examples

1. Accessing special variables:

SELECT x.* FROM OBJECTTABLE( 'dv_context' COLUMNS "user" string 'dv_row.userName' , row_number integer 'dv_row_number' ) AS x
SQL

The result is a row with two columns containing the username and 1, respectively.

Due to their mostly unrestricted access to Java functionality, usage of languages other than dv_script is restricted by default.

dv_script

dv_script is a simple scripting expression language that allows access to passing and special variables as well as any non-void 0-argument methods on objects and indexed values on arrays/lists. A dv_script expression begins by referencing the passing or special variable. Then any number of '.' accessors may be chained to evaluate the expression to a different value. Methods may be accessed by their property names, for example, foo rather than getFoo. If the object both a getFoo() and foo() method, the accessor for references foo() and getFoo() should be used to call the getter. An array or list index may be accessed using a 1-based positive integral value - using the same '.' accessor syntax. The same logic as the system function array_get is used meaning that null will be returned rather than an exception if the index is out of bounds.

dv_script is effectively dynamically typed as typing is performed at runtime. If an accessor does not exist on the object or if the method is not accessible then an exception will be thrown. If at any point in the accessor chain an expression evaluates to a null value, then null will be returned.

Examples

1. Get the VDB description string:

dv_context.session.vdb.description
SQL
2. Get the first character of the VDB description string:
dv_context.session.vdb.description.toCharArray. 1
SQL

3. Sleep function:

SELECT "x.result" 
FROM (OBJECTTABLE(     
	LANGUAGE 'javascript'     
	'java.lang.Thread.sleep(5000);     
	"success";'      
	COLUMNS "result" string 'dv_row') AS x);; 
SQL

This script executes a 5000 milliseconds long sleep function, that is it does deliberately nothing for 5 seconds. In order to have some output afterwards, the COLUMNS statement is used in combination with dv_row. The special iterator dv_row automatically gets the result of the last statement in the script. It is simply the string success in the example above. This will return the constant expression as the column result. If the result to return is a scalar value, the rowScript 'dv_row' is used.

4. How the output is made:

SELECT x.* 
FROM ( OBJECTTABLE(    
	LANGUAGE 'javascript' 
	'var rows = [];        
	firstrow = { "col1": "foo", "col2": "bar" };        
	rows.push( firstrow );        
	secondrow = { "col2": "foo", "col3": "bar" };        
	rows.push( secondrow );        
	rows;'         
	COLUMNS         
	"col1" string 'dv_row.col1',        
	"col2" string 'dv_row.col2',        
	"col3" string 'dv_row.col3',        
	"col4" string 'dv_row.col4' ) AS x);;
SQL

The script creates a collection of name rows and adds tuples onto it using the rows.push() function. Each tuple can have entirely different property names and values but they must all be present in the COLUMNS statement if they shall be returned. It is even possible to return non-existent properties. These will all be NULL. Note the notation of accessing the different properties: dv_row.<propertyName> and also remember that the collection rows has to be the last statement of the script.

5. JavaScript function appiled to a table column:

SELECT "k.Name","x.Reversed"  
FROM "SYS.DataTypes" AS "k", OBJECTTABLE(       
			LANGUAGE 'javascript' 
			'function reverse(s){ return s.split("").reverse().join(""); }                 
			reverse(tabname);'                  
			PASSING "k.Name" AS "tabname"                 
			COLUMNS  "Reversed" string 'dv_row' ) AS x;;
SQL

A script can also be applied to values from tables and views. The above script creates a function that reverses a string. Due to the combination with the table SYS.DataTypes, the script is executed for each row in the table. The PASSING statement declares that the value from k.Name shall be redirected as value for the variable tabname in the script. It is possible to pass constant expression, variable names from SQL scripts and table columns.