Basic Constructs

The basic building blocks of the OSQL query language are described here.

The Read-Eval-Print-Loop

OSQL statements can be evaluated immediately by SA Engine through its OSQL Read-Eval-Print-Loop, REPL. When using the SA Engine Console REPL in Unix/Windows shells, OSQL statements must be terminated by a semicolon (;). The ; is not needed when evaluating a single statement to the REPL in your browser or in SA Studio. You can enter more than one statement to the REPL by separating them with a ;, for example:

"Hello" + " world";
1+2+3;
sin(4) + sqrt(9) * 2;
rand(100)+1;

Constants

Constants can be integers, reals, strings, time stamps, booleans, or null. The value of a constant is the constant itself, for example:

123;
1.2;
2.3E2;
-2.4e-21;
'a string';
|2023-09-21T09:45:44.086Z|;
true;
false;
null;

Examples of strings:

"A string";
'A string';
'A string with "';
"A string with \" and '"

The enclosing string separators (' or ") for a string constant must be the same. If the string separator is " then \ is the escape character inside the string, replacing the succeeding character. For example the string 'ab"\' can also be written as "ab\"\\", and the string a'"b must be written as "a'\"b".

Boolean constants represent logical values. The constant false is equivalent to null casted to type Boolean. The only legal boolean value that can be stored in the database is true and a boolean value is regarded as false if it is not in the database (so called close world assumption).

Identifiers

Identifiers represent names of OSQL variables, functions, arrays, etc. Examples:

   MySalary
   sin
   Integer
   x
   x1234
   x1234_b

Notice that OSQL identifiers are NOT case sensitive; i.e. they are always internally capitalized. By contrast OSQL reserved keywords are always written with lower case letters.

Comments

A comment enclosed with /* and */can be placed anywhere in an OSQL statement outside identifiers, constants, strings, or variables. You can also use the // comment notation for single line comments.

Variables

Variables are of two kinds: local variables and session variables.

Local variables are identifiers for data values inside OSQL queries and functions. Variables are not case sensitive and internally capitalized.

For example, the following query uses a local variable named I;

select i
  from Number i
 where I = 3

Local variables must be declared in from clauses of queries or function signatures (see Defining Functions).

Session variables prefixed with : hold only temporary results during interactive sessions. Session variables cannot be referenced in function bodies and they are not stored in the database. Their lifespan is the current session only. Their purpose is to hold temporary values in scripts and database interactions.

Examples:

   :my_session_variable
   :MySessionVariable2

Session variables can be assigned by the variable assignment statement set, for example:

set :x3 = 2.3;          // set session variable :x3
set :i = 2 + sqrt(:x3); // set session variable :i
set :v = [1,2,3];       // set session variavle :v
:x3;                    // get value of :x3
:i;                     // get value of :i
:V;                     // get value of :v

You can inspect the type of the value to which a variable is assigned by the function typesig(Object). For example:

set :x3 = 2.3; 
set :v = [1,2,3];
typesig(:x3);
typesig(:v)

Expressions

Expressions are formulas expressed with the OSQL syntax that can be evaluated by the REPL to produce a value. Complex expressions can be built up in terms of other expression. Expressions are basic building blocks in all kinds of OSQL statements.

For example:

1.23;
1+2+3;
[1,2,3]

Not connected

To run this code block you must be logged in and your studio instance must be started.

Entering simple expressions followed by a semicolon is the simplest form of OSQL queries.

Collections

Collections represent sets of objects. OSQL supports five kinds of collections: bags, vectors, arrays, streams, and key-value associations (records):

• A bag is a set where duplicates are allowed.
• A vector is an ordered sequence of objects of any kind.
• A record is an associative array of key-value pairs.
• An array is a multi-dimensional collection of numbers.
• A stream is a possibly infinite sequence of objects that is continuously extended with new elements at the end.

Collections are constructed by collection constructor expressions. Examples:

bag(1,2,3);
[1,2,3];
[[1,2],[3,4]];
{"id":1,"name":"Kalle","age":32};
array([1,2,3])

Bags

A common kind of collection is bags, which are unordered sets of objects with duplicates allowed. The value of a query is usually a bag. When a query to an SA Engine REPL returns a bag as result the elements of the bag are printed on separate lines.

Bags can be explicitly created using the bag() constructor function, for example:

   bag(1,2,2,3)

The value of set queries are bags, for example:

select i 
  from Integer i 
 where i in bag(2,8,7)

Vectors

Vectors are sequences of objects of any kind. Square brackets [] enclose vector elements, For example, after evaluating the statement set :v=[1,2,3] the value of the session variable :v is [1,2,3].

Vector element $v_i$ can be accessed with the notation v[i], where the indexing i is from 1 and up. For example:

:v=[1,2,3];
:v[3]

Vectors are represented as objects of type Vector.

Section Vector queries documents vector functions and queries.

Arrays

Multi-dimensional arrays represent collections of numbers. 1D arrays represent numerical vectors, 2D arrays represent numberical matricies, and higher dimensionality arrays represent tensors.

Arrays are represented as objects of type Array. The difference between objects of type Array and type Vector is that vectors represent 1D sequences of objects of any kind, while 1D arrays very efficiently represent numerical vectors.

Arrays create created with the function array():

array([1,2,3]);         // a 1D array of integers
array([1.0,2.0,3.0]);   // a 1D array of reals
array([[1,2],[3,4]]);   // a 2x2 2D array of integers
array([[1.0,2],[3,4]]); // a 2x2 2D array of reals

Section Arrays and tensors documents array functions and queries.

Records

Records represent dynamic associations between keys and values. A record is a dynamic and associative array. Other commonly used terms for associative arrays are JSON objects, property lists, key-value pairs, dictionaries, or hash links. OSQL uses generalized JSON notation to construct records. For example the following expression assigns :r to a record where the key (property) 'Greeting' field has the value 'Hello, I am Tore' and the key 'Email' has the value 'Tore.Andersson@it.uu.se':

   set :r= {'Greeting':'Hello, I am Tore','Email':'Tore.Andersson@it.uu.se'}

A field f of a record bound to a variable r can be access with the notation r[f], for example: :r['Greeting' ] evaluates to Hello, I am Tore.

The type of records is called Record. The type Json represents JSON data. It is above both types Record and Vector in the type hierarchy, since JSON representations include both JSON objects and vectors.

Streams

Streams represent possibly infinite and growing sequences of elements, such as measurements produced by a sensor. Streams can also be generated (simulated) by functions. For example, the function heartbeat(p) returns an infinite stream of number elements produced every p seconds:

heartbeat(0.5) 

Streams are documented in section Streams.