...

-- Select all invoices where the supplier starts with 'ACM' and the index data field 'amount' is not NULL
SELECT * FROM invoice WHERE supplier LIKE 'ACM%' AND amount IS NOT NULL;

The LIKE expressions are limited by their number of included wildcards. Thus, a maximum number of 10 % and _ characters are allowed in one LIKE expression. The limit compliance is verified in the validation.

...

-- Select all documents where 'exceptional pie' was found and 'apple tree' was not found.
SELECT * FROM type where CONTAINS('+"exceptional pie" -"apple tree"')

...

Boosting

Boosting can be applied to the list of matches of full-text search queries. The boost operator ^ influences the relevance of individual terms within a CONTAINS statement. Thus, the sorting of the objects in the hit list is directly influenced.

The relevance of individual terms is specified via a boosting factor. The larger the boosting factor, the higher the relevance of the corresponding term. Here, the ratio between the individual boosting factors is considered and not the absolute values.

...

Escaping in CONTAINS Statements

In a CONTAINS expression, the system interprets specific symbols as operators. If you want to use those symbols as characters NOT beeing an operator, you have to escape them with a backslash ('\').

The following rules apply:

• The symbols < and > can not be escaped. If they are used in terms, they will be ignored.
• All types of quotation marks used within a term have to be escaped.
• The ^ symbol has to be escaped if not used as boosting operator.
• The ~ symbol has to be escaped if not used as fuzzy operator.
• Further special characters that have to be escaped: + - = & | ! ( ) { } [ ] * ? : \ /
• If an operator AND/OR is followed by a second operator AND/OR, the second operator will be automatically escaped and interpreted as a term and/or.

Weighted Score

The SCORE function returns the score values produced by the CONTAINS function in the SEARCH_SCORE field. The score value is a relative ranking, with values in the range of 0 to 1 and increases with the relevance. The SCORE function can also be queried with an alias name.

-- Select all documentsinvoices containing 'tree'. Prefer objects containing 'apple', 'pear' or 'chestnut'.
-- Prioritize 'pear' 4 times and 'chestnut' twice over 'apple'and their score value where the invoice amount is greater than 1000 and in fulltext either 'reminder', 'discount' or 'delay' was found.
SELECT *,SCORE() FROM type whereinvoice WHERE amount>1000 AND CONTAINS('applereminder pear^4 chestnut^2 +tree')

It is possible to reduce the relevance by applying a boosting value between 0 and 1.

discount delay');

-- SelectWith alias: Select all documents containing 'pie' and not 'tree'. Prefer objects containing 'apple' and especially 'pear'.
-- Avoid objects containing 'chestnut' invoices and their score value where the invoice amount is greater than 1000 and in fulltext either 'reminder', 'discount' or 'delay' was found.
SELECT *,SCORE() score FROM type whereinvoice WHERE amount>1000 AND CONTAINS('applereminder pear^2 chestnut^0.2 -tree +piediscount delay')

...

Fuzzy Search

In full-text search queries, partially matching objects can be provided as results. Thus, it is possible to search for similar terms or variant spellings.

The number of characters not matching the search term is called "edit distance". For each search term, a maximum edit distance can be defined that limits the number of unmatching characters.

Append the operator ~ followed by the integer maximum edit distance to a word within the CONTAINS statement. The values 0, 1 and 2 (default) are allowed for the edit distance. If the ~ operator is set without an edit distance, the default value 2 will be used. Since missing, redundant or reversed letters can be handled with edit distance 1, this value should be sufficient in most cases.

-- Select all objects containing words with maximum edit distance 1 to 'exceptionel' or 2 to 'cestnutt'.
-- E.g. objects containing 'exceptional' or 'chestnut' will be found.
SELECT * FROM type where CONTAINS('exceptionel~1 cestnutt~ ')

...

Proximity Search

It is possible to allow for reversed ordering of words in a phrase or to allow for additional words between the words of a phrase.

Here, the number of words not matching the specified phrase is called "edit distance". A maximum edit distance can be defined that limits the number of unmatching words.

...

;

Full-text search only in certain fields:

-- Select all invoices and their score value where the field 'status' either contains 'reminder', 'discount' or 'delay'.
SELECT *,SCORE() FROM invoice WHERE status CONTAINS('reminder discount delay');

Sorting with ORDER BY

To sort the result list, use the ORDER BY clause.

-- Select all invoices where the invoice amount is greater than 1000, with the result list sorted in descending order of the invoice amount.
SELECT * FROM invoice WHERE amount>1000 ORDER BY amount DESC;

-- Select all invoices where the invoice amount is greater than 1000,
-- with the result list sorted in descending order of the invoice amount and in ascending order of the date.
SELECT * FROM invoice WHERE amount>1000 ORDER BY amount DESC, date ASC;

In ORDER BY clauses, you can use the alias names given in the column definitions.

-- Select allthe documentsinvoice containingamount phraseswith ofalias maximum'b' editof distanceall 3invoices towhere the specifiedinvoice sentence.amount SELECTis *greater FROMthan type1000,
where CONTAINS('"Please be patient while this website is under construction."~3')

Escaping in CONTAINS Statements

In a CONTAINS expression, the system interprets specific symbols as operators. If you want to use those symbols as characters NOT beeing an operator, you have to escape them with a backslash ('\').

The following rules apply:

• The symbols < and > can not be escaped. If they are used in terms, they will be ignored.
• All types of quotation marks used within a term have to be escaped.
• The ^ symbol has to be escaped if not used as boosting operator.
• The ~ symbol has to be escaped if not used as fuzzy operator.
• Further special characters that have to be escaped: + - = & | ! ( ) { } [ ] * ? : \ /
• If an operator AND/OR is followed by a second operator AND/OR, the second operator will be automatically escaped and interpreted as a term and/or.

Weighted Score

The SCORE function returns the score values produced by the CONTAINS function in the SEARCH_SCORE field. The score value is a relative ranking, with values in the range of 0 to 1 and increases with the relevance. The SCORE function can also be queried with an alias name.

-- Select all invoices and their score value where the invoice amount is greater than 1000 and in fulltext either 'reminder', 'discount' or 'delay' was found.
SELECT *,SCORE() FROM invoice WHERE amount>1000 AND-- with the result list sorted in descending order of the alias b (invoice amount).
SELECT amount b FROM invoice WHERE amount>1000 ORDER BY b DESC;

To sort by the score value, you must assign an alias name to the SCORE () function.

-- Select all invoices and their score value where the field 'status' either contains 'reminder',
-- 'discount' or 'delay', with the result list sorted in ascending order of alias s (score value).
SELECT *,SCORE() s FROM invoice WHERE status CONTAINS('reminder discount delay'); ORDER --BY With alias: Select all invoices and their score value where the invoice amount is greater than 1000 and in fulltext either 'reminder', 'discount' or 'delay' was found.
SELECT *,SCORE() score FROM invoice WHERE amount>1000 AND CONTAINS('reminder discount delay');

...

s ASC;

Aggregations (COUNT(*), GROUP BY)

To determine the number of objects with special properties, queries can be aggregated. All objects with the same entry in the field specified after the GROUP BY clause are summarized. The number of objects is returned to the field 'OBJECT_COUNT' without the assignment of aliases.

-- Select the number of objects (enaio:object) with type specification, aggregated by the type of the object.
SELECT COUNT(*),type FROM system:object GROUP BY type;

It is also possible to aggregate several fields. In this case, all combinations of the fields mentioned are returned.

-- Select all invoices the number of objects (enaio:object) with type and theirtitle scorespecification, valueaggregated whereby the fieldtype 'status'and eithertitle contains 'reminder', 'discount' or 'delay'of the object.
SELECT COUNT(*,SCORE()),type,title FROM invoicesystem:object WHEREGROUP status CONTAINS('reminder discount delay');

Sorting with ORDER BY

...

BY type,title;

The GROUP BY function can be used without the COUNT(*) function. Then all combinations of the mentioned fields are returned without their numbers.

-- Select all'type' invoicesof whereall the invoice amount is greater than 1000, with the result list sorted in descending orderobjects (enaio:object), aggregated by the type of the invoice amountobject.
SELECT *type FROM invoicesystem:object WHEREGROUP amount>1000 ORDER BY amount DESC;

BY type;

To sort the result list of an aggregation, use the ORDER BY clause, with or without alias.

-- Select allthe invoicesnumber whereof the invoice amount is greater than 1000objects (enaio:object) with type specification,
-- aggregated withby the resulttype listof sortedthe in descending order ofobject, with the invoiceresult amountlist andsorted in ascending order of the date'type'.
SELECT COUNT(*),type FROM enaio:object invoiceGROUP WHEREBY amount>1000type ORDER BY amounttype DESC,ASC;
date
ASC;

In ORDER BY clauses, you can use the alias names given in the column definitions.

-- With alias: Select the invoice amountnumber and 'type' with alias 'bt' of all invoices where the invoice amount is greater than 1000,
--objects (enaio:object),
-- aggregated by 'type', with the result list sorted in descending order of the alias bt (invoice amounttype).
SELECT amount bCOUNT(*),type t FROM system:object invoiceGROUP WHEREBY amount>1000type ORDER BY bt DESC;

To In order to be able to sort by the score value, you must assign an alias name to the SCORE () function.

-- Select allthe invoicesnumber andwith theiralias score'c' valueand where'type' theof field 'status' either contains 'reminderall objects (enaio:object), aggregated by 'type',
-- 'discount' or 'delay', with the result list sorted in ascendingdescending order of alias sc (scorenumber of valueobjects).
SELECT COUNT(*,SCORE() sc,type FROM invoicesystem:object WHEREGROUP status CONTAINS('reminder discount delay')BY type ORDER BY sc ASC;

Aggregations (COUNT(*), GROUP BY)

To determine the number of objects with special properties, queries can be aggregated. All objects with the same entry in the field specified after the GROUP BY clause are summarized. The number of objects is returned to the field 'OBJECT_COUNT' without the assignment of aliases.

-- Select the number of objects (enaio:object) with type specification, aggregated by the type of the object.
SELECT COUNT(*),type FROM system:object GROUP BY type;

It is also possible to aggregate several fields. In this case, all combinations of the fields mentioned are returned.

-- Select the number of objects (enaio:object) with type and title specification, aggregated by the type and title of the object.
SELECT COUNT(*),type,title FROM system:object GROUP BY type,title;

The GROUP BY function can be used without the COUNT(*) function. Then all combinations of the mentioned fields are returned without their numbers.

-- Select 'type' of all objects (enaio:object), aggregated by the type of the object.
SELECT type FROM system:object GROUP BY type;

To sort the result list of an aggregation, use the ORDER BY clause, with or without alias.

-- Select the number of objects (enaio:object) with type specification,
-- aggregated by the type of the object, with the result list sorted in ascending order of 'type'.
SELECT COUNT(*),type FROM enaio:object GROUP BY type ORDER BY type ASC;

-- With alias: Select the number and 'type' with alias 't' of all objects (enaio:object),
-- aggregated by 'type', with the result list sorted in descending order of alias t (type).
SELECT COUNT(*),type t FROM system:object GROUP BY type ORDER BY t DESC;

In order to be able to sort by the score value, you must assign an alias name to the SCORE () function.

-- Select the number with alias 'c' and 'type' of all objects (enaio:object), aggregated by 'type',
-- with the result list sorted in descending order of alias c (number of objects).
SELECT COUNT(*) c,type FROM system:object GROUP BY type ORDER BY c DESC;

...

Provided Date Functions in WHERE Clauses

The query language provides some frequently used time and time span functions for WHERE clauses.

• today() - a date field contains today's date
• yesterday() - a date field contains yesterday's date
• thisweek() - a date field contains this week's date, a week starts on Monday and ends on Sunday
• lastweek() - a date field contains last week's date, a week starts on Monday and ends on Sunday
• thismonth() - a date field contains this month's date
• lastmonth() - a date field contains last month's date
• thisyear() - a date field contains this year's date
• lastyear() - a date field contains last year's date
• now() - the current date 

  Status
  colour Blue title 2.0

• dateadd(interval, number, date) - adds a date interval to a date and returns the date. [from yuuvis 2019 Winter]
  
  • valid interval values: year, yyyy, yy = Year; quarter, qq, q = Quarter; month, mm, m = month; dayofyear = Day of the year; day, dy, y = Day; week, ww, wk = Week; weekday, dw, w = Weekday; hour, hh = hour; minute, mi, n = Minute; second, ss, s = Second; millisecond, ms = Millisecond
  • number can be a positiv or negative integer value
  • date is a valid ISO8601 date value or a function which delivers a valid ISO8601 date value

SELECT ... FROM ... WHERE system:creationDate IN today();
SELECT ... FROM ... WHERE rmDestructionDate > today();

SELECT contentStreamId, fileName, repositoryId FROM system:object WHERE system:creationDate IN lastmonth();
SELECT * FROM document WHERE system:lastModificationDate IN today() AND system:creationDate IN lastyear() ORDER BY system:creationDate DESC;
SELECT COUNT(*) c, system:creationDate FROM enaio:object WHERE system:creationDate IN thisyear() GROUP BY system:creationDate ORDER BY c DESC;

SELECT * FROM system:object WHERE system:rmDestructionDate < now();
SELECT * FROM system:object WHERE system:creationDate < dateadd('day',35,'2018-09-20T08:37:26.280Z');
SELECT * FROM document WHERE system:lastModificationDate > dateadd('year',-1,now());

Queries on contentstream Fields

Additional to property fields of a DMS object, all contentstream fields can be queried in SELECT statements as described above. The following queriable contentstream fields exist:

• contentStreamId- the ID of the contentstream
• repositoryId- the ID of the repository the content is stored in
• mimeType - the mime type of the content
• fileName - the file name of the content
• length - the length in bytes of the file
• range - for compound documents the byte range which represents the object content in the file 
• digest - the sha256 hash of the content
• archivePath - the file path within an archive

SELECT contentStreamId, fileName, repositoryId FROM system:object;
SELECT * FROM document WHERE mimeType'message/rfc822' ORDER BY title DESC;
SELECT COUNT(*) c, fileName FROM system:object GROUP BY fileName ORDER BY c DESC;

...

Queries on Queriable Tables

As of version 2020 Winter, table cells can be accessed using the format in the following example:

-- syntax of table cell access
SELECT * FROM system:object WHERE queriable_table[row].column = value;          

-- examples
SELECT * FROM system:object WHERE queriable_table[row].columnx = value;
SELECT queriable_table.nickname, queriable_table.name FROM system:object WHERE queriable_table[row].columnx LIKE 'steven';
SELECT * FROM system:object WHERE (queriable_table[5].age <= 55 AND queriable_table[6].age >= 22) OR queriable_table[6].age > 6;
SELECT * FROM system:object WHERE queriable_table[2].name IN ('Erwin', 'Max');
SELECT * FROM system:object WHERE queriable_table[3].name CONTAINS('Arno');
SELECT * FROM system:object WHERE queriable_table[4].city IS NULL;
SELECT * FROM system:object WHERE queriable_table[4].city IS NOT NULL;
SELECT * FROM system:object WHERE queriable_table[5].date > lastyear();

Between the SELECT and FROM keywords, one or more table columns can be referenced with the syntax tablename.columnname as shown in the second example query above. The query will then return only the selected columns, in the example the columns nickname and name of the table queriable_table. Otherwise, like in the other examples above where the * symbol is placed between the SELECT and FROM keywords, the entire table will be returned.

The conditions are specified in the same way like in single query calls. Date functions can be used, too.

ORDER BY and GROUP BY are not supported on tables.

To reference any column or any row in the condition, the * symbol can be used as wildcard, as shown in the following examples:

SELECT * FROM system:object WHERE queriable_table[*].columnx = value;  -- columnx should have the given value in any row
SELECT * FROM system:object WHERE queriable_table[rowx].* = value;     -- any column should have the given value in rowx
SELECT * FROM system:object WHERE queriable_table[*].* = value;        -- any table cell should have the given value

For combined queries within one column of a table, there is a special syntax as shown in the code block below. Examples are provided as well.

-- syntax of table cell access
SELECT * FROM system:object WHERE queriable_table[row].(column_condition_1 AND/OR ... AND/OR column_condition_x);
SELECT * FROM system:object WHERE queriable_table[*].(column_condition_1 AND/OR ... AND/OR column_condition_x);

-- examples          
SELECT * FROM system:object WHERE queriable_table[*].(name IN ('Erwin', 'Max') AND age >= 22);  
SELECT * FROM system:object WHERE queriable_table[5].((name CONTAINS('Arno') AND age >= 22) OR city IS NULL);
SELECT * FROM system:object WHERE queriable_table[*].((name CONTAINS('Arno') AND age >= 22) OR (city IS NULL and date <= lastyear())) AND system:creationdate IN thisyear();

Within the brackets, any complex condition statement created from the set of conditions on properties described in the sections above can be queried, where each single condition will be applied on the specified row. If you use the wildcard * as row number, each table row will be tested on the complex condition. For a query match, a row has to be found for which the whole condition set is true.

...

Queries on Tags

As of version 2020 Summer, tags can be assigned to objects. The tag properties are searchable by means of queries. Since the tag properties are stored in table format, the queries are defined like queries on tables. Each tag corresponds to one row of the table property system:tags that is assigned to every object. The row is indicated by the value of the corresponding tag's name. The four available columns are the same for all tags: name, state, creationDate and traceId. The following code block shows examples:

SELECT * FROM system:object WHERE system:tags[*].creationDate=TODAY();
SELECT * FROM system:object WHERE system:tags[*].name='ren:ocr';
SELECT * FROM system:object WHERE system:tags[ren:ocr].state=2;
SELECT * FROM system:object WHERE system:tags[*].(state=2 AND creationDate=TODAY());
SELECT * FROM system:object WHERE system:tags IS NULL;
SELECT * FROM system:object WHERE system:tags[ren:ocr1] IS NULL;

Of course, the queries can be combined with any other valid query as can be seen in the following examples:

-- search for objects have a tag created today and with the state 2
SELECT * FROM system:object WHERE system:tags[*].(state=2 AND creationDate=TODAY());    

-- search for objects having the tag 'analysis' with the state 1 that were created yesterday
SELECT * FROM system:object WHERE system:tags[analysis].state=1 AND where system:creationDate=YESTERDAY();

...

Queries on Structured Data

As of version 2021 Summer, structured data properties can be used to assign an arbitrary JSON structure to objects. The structured data properties are stored similarly to table properties and are thus searchable by means of similar queries. The addressing of individual keys within the JSON structure is inspired by the concept of JSONPath.

Conditions on Structured Data

The following code block shows examples where objects of type order are searched that match the corresponding WHERE condition. The example property customerdetails is a structured data property referenced in the object type definition for order. If objects are found, the values for the structured data property customerdetail will be returned in JSON format for each of them.

-- Search for objects where 'customerdetails' contains the first-order key 'id' with value '2982'.
SELECT customerdetails FROM order WHERE customerdetails.id = 2982

-- Search for objects where 'customerdetails' contains the first-order key 'words' beeing a list with an existing value for index 10.
SELECT customerdetails FROM order WHERE customerdetails.words[10] IS NOT NULL

-- Search for objects where 'customerdetails' contains the first-order key 'words' beeing a list that contains the entry 'milk' at an arbitrary index.
SELECT customerdetails FROM order WHERE customerdetails.words[*] = 'milk'

-- Search for objects where 'customerdetails' contains the first-order key 'sentences' beeing a list that contains the term 'milk' in the second element.
SELECT customerdetails FROM order WHERE customerdetails.sentences[1] CONTAINS('milk')

-- Search for objects where 'customerdetails' contains the first-order key 'food' containing the second-order datetime key 'lastcooked' that has to be a value earlier than last year.
SELECT customerdetails FROM order WHERE customerdetails.food.lastcooked < LASTYEAR()

-- Search for objects where 'customerdetails' contains 'jelly' in the value for the key 'ingredient' that can be at any hierarchical level within the JSON.
SELECT customerdetails FROM order WHERE customerdetails..ingredient CONTAINS('jelly')

-- Search for objects where 'customerdetails' contains 'jelly' in the value for the key 'ingredient' that can be at any hierarchical level within the JSON,
-- but needs to have a direct or indirect parent node 'food'.
SELECT customerdetails FROM order WHERE customerdetails..food..ingredient CONTAINS('jelly')

Restriction Possibilities for Responses

To return only a sub-structure of the JSON value for a structured data property, specify the corresponding key as SELECT statement:

SELECT customerdetails.uid FROM order

To return multiple sub-structures, separate them by comma:

SELECT customerdetails.uid,customerdetails.word FROM order

If you specify an index within a list, the values for the list elements with lower indices will be replaced by null in the return statement:

-- The query starting with ...
SELECT customerdetails.words[2] FROM order

-- ... will result in the return statement:
{
	"properties": {
		"appTable:customerdetails": {
			"value": {
				"words": [
					null,
					null,
					"water"
				]
			}
		}
	}
}

To return sub-structures located at any hierarchical level within the JSON value, use .. as shown in the examples below.

-- Search for objects of type order and display only key-value mappings for keys 'uid'.
SELECT customerdetails..uid FROM order

-- Example response:
{
	"properties": {
		"appTable:customerdetails": {
			"value": {
				"uid": "711e1858-eb24-4183-8743-0292c7b9b93b",
                                 "food": {
    DESC;

Provided Date Functions in WHERE Clauses

The query language provides some frequently used time and time span functions for WHERE clauses.

• today() - a date field contains today's date
• yesterday() - a date field contains yesterday's date
• thisweek() - a date field contains this week's date, a week starts on Monday and ends on Sunday
• lastweek() - a date field contains last week's date, a week starts on Monday and ends on Sunday
• thismonth() - a date field contains this month's date
• lastmonth() - a date field contains last month's date
• thisyear() - a date field contains this year's date
• lastyear() - a date field contains last year's date
• now() - the current date 

  Status
  colour Blue title 2.0

• dateadd(interval, number, date) - adds a date interval to a date and returns the date. [from yuuvis 2019 Winter]
  
  • valid interval values: year, yyyy, yy = Year; quarter, qq, q = Quarter; month, mm, m = month; dayofyear = Day of the year; day, dy, y = Day; week, ww, wk = Week; weekday, dw, w = Weekday; hour, hh = hour; minute, mi, n = Minute; second, ss, s = Second; millisecond, ms = Millisecond
  • number can be a positiv or negative integer value
  • date is a valid ISO8601 date value or a function which delivers a valid ISO8601 date value

SELECT ... FROM ... WHERE system:creationDate IN today();
SELECT ... FROM ... WHERE rmDestructionDate > today();

SELECT contentStreamId, fileName, repositoryId FROM system:object WHERE system:creationDate IN lastmonth();
SELECT * FROM document WHERE system:lastModificationDate IN today() AND system:creationDate IN lastyear() ORDER BY system:creationDate DESC;
SELECT COUNT(*) c, system:creationDate FROM enaio:object WHERE system:creationDate IN thisyear() GROUP BY system:creationDate ORDER BY c DESC;

SELECT * FROM system:object WHERE system:rmDestructionDate < now();
SELECT * FROM system:object WHERE system:creationDate < dateadd('day',35,'2018-09-20T08:37:26.280Z');
SELECT * FROM document WHERE system:lastModificationDate > dateadd('year',-1,now());

Queries on contentstream Fields

Additional to property fields of a DMS object, all contentstream fields can be queried in SELECT statements as described above. The following queriable contentstream fields exist:

• contentStreamId- the ID of the contentstream
• repositoryId- the ID of the repository the content is stored in
• mimeType - the mime type of the content
• fileName - the file name of the content
• length - the length in bytes of the file
• range - for compound documents the byte range which represents the object content in the file 
• digest - the sha256 hash of the content
• archivePath - the file path within an archive

SELECT contentStreamId, fileName, repositoryId FROM system:object;
SELECT * FROM document WHERE mimeType'message/rfc822' ORDER BY title DESC;
SELECT COUNT(*) c, fileName FROM system:object GROUP BY fileName ORDER BY c DESC;

Queries on Queriable Tables

As of version 2020 Winter, table cells can be accessed using the format in the following example:

-- syntax of table cell access
SELECT * FROM system:object WHERE queriable_table[row].column = value;          

-- examples
SELECT * FROM system:object WHERE queriable_table[row].columnx = value;
SELECT queriable_table.nickname, queriable_table.name FROM system:object WHERE queriable_table[row].columnx LIKE 'steven';
SELECT * FROM system:object WHERE (queriable_table[5].age <= 55 AND queriable_table[6].age >= 22) OR queriable_table[6].age > 6;
SELECT * FROM system:object WHERE queriable_table[2].name IN ('Erwin', 'Max');
SELECT * FROM system:object WHERE queriable_table[3].name CONTAINS('Arno');
SELECT * FROM system:object WHERE queriable_table[4].city IS NULL;
SELECT * FROM system:object WHERE queriable_table[4].city IS NOT NULL;
SELECT * FROM system:object WHERE queriable_table[5].date > lastyear();

Between the SELECT and FROM keywords, one or more table columns can be referenced with the syntax tablename.columnname as shown in the second example query above. The query will then return only the selected columns, in the example the columns nickname and name of the table queriable_table. Otherwise, like in the other examples above where the * symbol is placed between the SELECT and FROM keywords, the entire table will be returned.

The conditions are specified in the same way like in single query calls. Date functions can be used, too.

ORDER BY and GROUP BY are not supported on tables.

To reference any column or any row in the condition, the * symbol can be used as wildcard, as shown in the following examples:

SELECT * FROM system:object WHERE queriable_table[*].columnx = value;  -- columnx should have the given value in any row
SELECT * FROM system:object WHERE queriable_table[rowx].* = value;     -- any column should have the given value in rowx
SELECT * FROM system:object WHERE queriable_table[*].* = value;        -- any table cell should have the given value

For combined queries within one column of a table, there is a special syntax as shown in the code block below. Examples are provided as well.

-- syntax of table cell access
SELECT * FROM system:object WHERE queriable_table[row].(column_condition_1 AND/OR ... AND/OR column_condition_x);
SELECT * FROM system:object WHERE queriable_table[*].(column_condition_1 AND/OR ... AND/OR column_condition_x);

-- examples          
SELECT * FROM system:object WHERE queriable_table[*].(name IN ('Erwin', 'Max') AND age >= 22);  
SELECT * FROM   "uid"system: "7aa4a2f2-3dc0-420c-a0d7-edc6af3619de"
                                 }
			}
		}
	}
}

-- Search for objects of type order and display only key-value mappings for keys 'uid' that have a direkt or indirect parent node 'food'.
SELECT customerdetails..food..uid FROM order

-- Example response:
{
	"properties": {
		"appTable:customerdetails": {
			"value": {
                                 "food": {
                                   "uid": "7aa4a2f2-3dc0-420c-a0d7-edc6af3619de"
                                 }
			}
		}
	}
}object WHERE queriable_table[5].((name CONTAINS('Arno') AND age >= 22) OR city IS NULL);
SELECT * FROM system:object WHERE queriable_table[*].((name CONTAINS('Arno') AND age >= 22) OR (city IS NULL and date <= lastyear())) AND system:creationdate IN thisyear();

Within the brackets, any complex condition statement created from the set of conditions on properties described in the sections above can be queried, where each single condition will be applied on the specified row. If you use the wildcard * as row number, each table row will be tested on the complex condition. For a query match, a row has to be found for which the whole condition set is true.

As shown in the last two lines of the code block above, Combined queries on a row can also be used in combination with further conditions.

Queries on Tags

As of version 2020 Summer, tags can be assigned to objects. The tag properties are searchable by means of queries. Since the tag properties are stored in table format, the queries are defined like queries on tables. Each tag corresponds to one row of the table property system:tags that is assigned to every object. The row is indicated by the value of the corresponding tag's name. The four available columns are the same for all tags: name, state, creationDate and traceId. The following code block shows examples:

SELECT * FROM system:object WHERE system:tags[*].creationDate=TODAY();
SELECT * FROM system:object WHERE system:tags[*].name='ren:ocr';
SELECT * FROM system:object WHERE system:tags[ren:ocr].state=2;
SELECT * FROM system:object WHERE system:tags[*].(state=2 AND creationDate=TODAY());
SELECT * FROM system:object WHERE system:tags IS NULL;
SELECT * FROM system:object WHERE system:tags[ren:ocr1] IS NULL;

Of course, the queries can be combined with any other valid query as can be seen in the following examples:

-- search for objects have a tag created today and with the state 2
SELECT * FROM system:object WHERE system:tags[*].(state=2 AND creationDate=TODAY());    

-- search for objects having the tag 'analysis' with the state 1 that were created yesterday
SELECT * FROM system:object WHERE system:tags[analysis].state=1 AND where system:creationDate=YESTERDAY();

Queries on Audit Entries

Audit entries can be queried by querying the object type enaio:audit.

...