Introduction

Last published : Aug 10, 2026
Related information

Overview

This documentation covers Structured Query Language (SQL) queries and expressions in EBX®. EBX® supports standard SQL queries to retrieve rows selected from one or more tables. Some EBX® SQL language features are extensions to the standard. Supported EBX® SQL syntax includes: table expressions (SELECT, FROM, WHERE, GROUP BY and HAVING clauses), DISTINCT, ORDER BY, LIMIT and OFFSET, combining queries (UNION ), and WITH (except RECURSIVE modifier).
The goal of this API is to provide to developers the ability to retrieve data from EBX® using a well-known standard.
EBX® SQL is accessible through Java APIs, especially from the class Query. The queries also support parameters. See Query.setParameter.
Queries can involve a single dataset or multiple datasets, see QueryBuilder to know more about how to create a query.

Mapping data model entities

The following section provides a detailed explanation about the mapping of the EBX® concepts into SQL.

Table (in data model)

EBX® tables are mapped naturally to SQL tables. In the data model, there can be more than one EBX® table with the same name. This ambiguity can occur when tables are in groups. To remove the ambiguity, use the full path of the table surrounded by double quotes (for example, "my_group/my_table" no longer conflicts with "other_group/my_table"). You can also use the entity name of the table, which is unique inside the data model. You can use the table name only if it does not collide with an entity name or another table name.
See also:

Fields

In SQL Standard, the structure of a table consists of one or more columns. Every element (including fields) whose parent is an EBX® table, is mapped to a column.

Groups

In SQL Standard, the structure of a table consists of one or more columns. Every element whose parent is an EBX® table is mapped to a column. This includes groups that are mapped to SQL columns as SQL structure types.

Associations

In SQL Standard, querying data among multiple tables is based on foreign keys and primary keys. These concepts in EBX® are similar to those in SQL. Therefore, joins between tables in SQL can also be done using EBX® foreign and primary keys.

Mapping data types

Handling data through SQL is highly dependent on its data type. For example, in predicates, columns can be compared only if they have the same SQL data type. The SQL data types are types according to the type in the data model.

Simple data types

Supported standard SQL data types
This table lists all of the simple types defined in the XML Schema that are supported by EBX®, along with their corresponding standard SQL types.
XML Schema type SQL type Java type Notes
xs:string VARCHAR java.lang.String
xs:boolean BOOLEAN java.lang.Boolean Values: TRUE, FALSE, UNKNOWN
xs:decimal DECIMAL java.math.BigDecimal
xs:dateTime TIMESTAMP java.util.Date
xs:time TIME java.util.Date The date portion of the returned Date is always set to '1970/01/01'.
xs:date DATE java.util.Date The time portion of the returned Date is always set to the beginning of the day (that is, '00:00:00').
xs:anyURI VARCHAR java.net.URI
xs:Name ( xs:string restriction) VARCHAR java.lang.String
xs:int INT java.lang.Integer

Extended simple types defined by EBX®

EBX® provides pre-defined simple data types. These types are defined by the internal schema common-1.0.xsd. Their definition is detailed in the section Extended simple types defined by EBX®
XML Schema type SQL type Java class
osd:text ( xs:string restriction) VARCHAR java.lang.String
osd:html ( xs:string restriction) VARCHAR java.lang.String
osd:email ( xs:string restriction) VARCHAR java.lang.String
osd:password ( xs:string restriction) VARCHAR java.lang.String
osd:color ( xs:string restriction) VARCHAR java.lang.String
osd:resource ( xs:anyURI restriction) VARCHAR internal class
osd:locale ( xs:string restriction) VARCHAR java.util.Locale
osd:dataspaceKey ( xs:string restriction) VARCHAR java.lang.String
osd:datasetName ( xs:string restriction) VARCHAR java.lang.String

List (multi-valued) types

Lists are handled as SQL Arrays. Their corresponding Java class is java.util.List.
Note: in order to filter on a multivalued field, first it has to be "normalized" via the UNNEST SQL operator (be aware that this is not recommended in terms of performance and can be quite inefficient on large tables):
  • SELECT T.s FROM myTable T, UNNEST(T.list) as L(item) WHERE L.item = 'test'
  • SELECT p.name FROM Person p, UNNEST(p.addresses) AS ad(street, city) WHERE ad.city = 'Paris'

Complex types

Complex types are handled as SQL Structured types. Their corresponding Java class is Object[]. This applies to foreign keys (see below) and groups, because they are defined through complex types. Use the dot operator to access fields inside the SQL Structure types. For example, use address.street to access the field street of the field address, if it is a complex type. When you reference a sub-field of a complex type in a query, you must always use the table name or alias:
  • SELECT customer.address.street FROM customer
  • SELECT c.address.street FROM customer c

TableRef types

In EBX®, a table can have a primary key composed of multiple fields. Foreign keys are defined by a single field with the osd:tableRef declaration. The standard SQL syntax has been extended to extract the value of any targeted primary key field. In the Extraction of foreign keys example, the following SQL expressions are valid:
  • tableA.fkb.id = 123
  • YEAR(tableA.fkb.date) > 2018
Note:
Even if the primary key is composed of only one field, the name of the field must be specified to access the value. For example, if the primary key is composed of a single id, fkb.id must be used to access the value, as in tableA.fkb.id = 123
A typical example of tableRef usage would be SQL joins:
SELECT * FROM employee JOIN department ON employee.fkDept.id = department.id
Or in case the referenced table has a composite primary key:
SELECT * FROM tableA JOIN tableB ON tableA.fkB.id1 = tableB.id1 AND tableA.fkB.id2 = tableB.id2
Analogously, we could perform the same JOIN via the string representation of the key, using FK_AS_STRING built-in function and $pk system column:
SELECT * FROM tableA JOIN tableB ON FK_AS_STRING(tableA.fkB) = tableB."$pk"

Metadata fields

EBX® SQL provides also access to some metadata information. These fields are not returned by a SQL statement, unless they are explicitly referenced. For example, " SELECT * FROM ..." does not return metadata fields, but " SELECT metadataField FROM ..." does.
Name Description SQL type Java class Examples
creator The creator of the record. VARCHAR java.lang.String SELECT t."ebx-metadata"."system"."creator" FROM myTable t SELECT t.* FROM myTable t WHERE t."ebx-metadata"."system"."creator" = 'Uuser1' SELECT t.* FROM myTable t ORDER BY t."ebx-metadata"."system"."creator"
creation_time The creation time of the record. TIMESTAMP java.util.Date SELECT t."ebx-metadata"."system"."creation_time" FROM myTable t SELECT t.* FROM myTable t WHERE t."ebx-metadata"."system"."creation_time" > TIMESTAMP'2001-06-10 14:57:20' SELECT t.* FROM myTable t ORDER BY t."ebx-metadata"."system"."creation_time"
updater The last user who modified the record. VARCHAR java.lang.String SELECT t."ebx-metadata"."system"."updater" FROM myTable t SELECT t.* FROM myTable t WHERE t."ebx-metadata"."system"."updater" = 'Uuser1' SELECT t.* FROM myTable t ORDER BY t."ebx-metadata"."system"."updater"
update_time The time of the last modification of the record. TIMESTAMP java.util.Date SELECT t."ebx-metadata"."system"."update_time" FROM myTable t SELECT t.* FROM myTable t WHERE t."ebx-metadata"."system"."update_time" > TIMESTAMP'2001-06-10 14:57:20' SELECT t.* FROM myTable t ORDER BY t."ebx-metadata"."system"."update_time"

System columns

Apart from the fields present in a table, EBX® SQL provides some extra system columns. These columns are not returned by a SQL statement, unless they are explicitly referenced. For example, " SELECT * FROM ..." does not return systems columns, but " SELECT systemColumnName FROM ..." does.
Name
Description
SQL type
Java class
Examples
$adaptation
The Adaptation representing the table record.
internal type
com.onwbp.adaptation.Adaptation
SELECT t."$adaptation" FROM myTable t WHERE t.value>100
$pk
String representation of the primary key of the record.
See also:
VARCHAR
java.lang.String
SELECT t.* FROM myTable1 t WHERE t."$pk"='123'
SELECT t.* FROM myTable2 t WHERE t."$pk"='123|abc'
SELECT t."$pk" FROM myTable3 t WHERE t.value>100
SELECT t.value FROM myTable3 t ORDER BY t."$pk"

SQL syntax

Supported standard operators and functions

An operator is a reserved word or a character used primarily in a SQL statement's WHERE clause to perform operation(s), such as comparisons and arithmetic operations. These Operators are used to specify conditions in a SQL statement, and to serve as conjunctions for multiple conditions in a statement. EBX® supports most of the SQL standard operators and functions. Some functions and operators can have optional parameters: they are surrounded by square brackets in the documentation. Generally, there are five types of operators and functions:
Operator Associativity
. left
(array element) left
+ - (unary plus, minus) right
* / % left
+ - left
BETWEEN, IN, LIKE, CONTAINS, and so on -
< > = <= >= <> != left
IS NULL, IS FALSE, IS NOT TRUE, and so on -
NOT right
AND left
OR left

Escaping identifiers

In the following cases, the identifier must be escaped by using double quotes:
  • when using the absolute path to identify a table (for example, "/root/myTable").
  • when the field to identify is a reserved word (for example, "user", "order").
  • when referring to a system column with a table alias (for example, t."$adaptation", t."$pk").
  • when referring to a metadata field (for example, t."ebx-metadata"."system"."creation\_time").
The following example shows a query to illustrate all cases:
SELECT t."user", t."$pk" FROM "/root/myTable" t WHERE t."order" = 1

Explain plan

EBX® SQL supports EXPLAIN PLAN FOR ... syntax to get the plan information of a query.
The result is similar to Query.explain.
Example: EXPLAIN PLAN FOR SELECT id FROM myTable

Limitations and performance guidelines

  • Certain internal join optimizations do not support RIGHT and FULL joins, so avoid these join types if possible.
  • The maximum precision and scale for numeric or decimal values is 1000.
  • Queries using GROUP BY and/or aggregate functions ( MIN, MAX, and so on) are not optimized, except for COUNT, which can be optimized in some circumstances.
  • Currently, MIN and MAX operators do not exploit internal indices. Instead, use the following equivalent queries, which are probably more efficient:
    SELECT val FROM myTable ORDER BY val DESC NULLS LAST LIMIT 1 instead of SELECT MAX(val) FROM myTable
    SELECT val FROM myTable ORDER BY val LIMIT 1 instead of SELECT MIN(val) FROM myTable