Custom filter query syntax

Context

This document describes the syntax used to query a filtered list of sources or sites using the Opinum API or the source (meter) search box in Opinum Data Hub portal.

Queries

Relational operators

Note

All string operations are case-insensitive Operator names are case insensitive

Operator	Description	Example
left = right	True if left is equal to right (see Relaxed type interpretation)	Name = ‘My name’, id = 123
left <> right left != right	True if left is not equal to right (see Relaxed type interpretation)	Name <> ‘My name’, id != 123
left > right	True if left is greater than right. This operator is more meaningful when comparing numeric/date values but is also valid for strings (‘AB’> ‘A’)	‘my custom field’ > -123E-6
left >= right	True if left is greater than right. This operator is more meaningful when comparing numeric/date values but is also valid for strings (‘AB’ >= ‘A’)	‘my custom date’ >= 2018
left < right	True if left is greater than right. This operator is more meaningful when comparing numeric/date values but is also valid for strings (‘A’ < ‘B’)	id < 10
left <= right	True if left is greater than right. This operator is more meaningful when comparing numeric/date values but is also valid for strings (‘AB’ <= ‘ABC’)	id <= 10
left ~ right left like right left comme right left zoals right	True if left contains right string. This operator only works for string types	name like abc
left !~ right left !like right left !comme right left !zoals right	True if left does not contain right string. This operator only works for string types.	name !~ abc
left startswith right left commencepar right left beginmet right	True if left starts with right string. This operator only works for string types	name commencepar abc
left !startswith right left !commencepar right left !beginmet right	True if left does not start with right string. This operator only works for string types	name ! beginmet 123
left endswith right left finitpar right left eindigtmet right	True if left ends with right string. This operator only works for string types	name endswith abc
left !endswith right left !finitpar right left !eindigtmet right	True if left does not end with right string. This operator only works for string types	name !endswith abc

Find entities by value

identifier relational_operator value

The identifier is searched among all property names, whether they are system (not belonging to any forms like Name, Ean, Id, …) or not (non-system properties are defined in forms).

Example:

Name like acme which matches Name = 'my acme name' and Form1.Group1.Name = 'acme' (see Find form entities by value)
SerialNumber > 10

Find system entities by value

[identifier] relational_operator value

The identifier is searched among all property names not belonging to any forms like Name, Ean, Id…

[Name] like acme which matches Name = 'my acme name' but not Form1.Group1.Name = 'acme' (see Find form entities by value)

Find form entities by value

form_identifier.group_identifier.field_identifier relational_operator value

The identifier is searched among all property names belonging to forms like Name, Ean, Id…

Form1.Group1.Name like acme which matches Form1.Group1.Name having value 'acme' (see Find form entities by value) but not system property Name having value 'my acme ne'

Note

group_identifier is optional: Form1.Name = acme will search for any field of Form1 having a property called Name with value acme.

form_identifier and group_identifier (as a pair) is optional: form_field = acme will search for any field of any form having a property called Name with value acme. Remember that this query will also search for system property named form_field.

Find entities with empty properties

identifier = empty, identifier != empty, identifier <> empty

Searches for entities having an identifier whose value is/is not either null or empty string or blank string.

Note

identifier = empty can't find entities MISSING a property called identifier. It will find entities having identifier = null or identifier = ''

Find entities based on a foreign entity

Site{expression}, Lieu{expression}, Locatie{expression}

Searches for sources related to sites satisfying the expression.

Example

Site{ Name like acme and PostalCode > 1000} : select all sources linked to a site satisfying Name like acme and PostalCode > 1000
Site{ Name like acme and PostalCode > 1000} and Name like MyName : select all sources satisfying Name like MyName and linked to a site satisfying Name like acme and PostalCode > 1000

Note

This is only meaningful when searching for Sources.

Source{expression}, Meter{expression}, Equipement{expression}, Bron{expression}

Searches for entities related to sources satisfying the expression.

Example

Source{ Name like acme} : select all sites linked to a source satisfying Name like acme
Source{ Name like acme} and PostalCode > 1000 : select all sitessatisfying PostalCode > 1000 and linked to a source satisfying Name like acme

Note

This is only meaningful when searching for Sites.

DataQuality{quality_value}, Qualite{ quality_value }, Kwaliteit{ quality_value }

Searches for sources having the specified quality_value. The quality values are:

GOOD, BON, GOED: select sources having all their variables (involved in a data quality status check) reporting a valid data quality status
BAD, MAUVAIS, MAUVAISE, SLECHT: select sources having at least one of their variables (involved in a data quality status check) reporting a bad data quality status
UNDEFINED, INDEFINI, INDEFINIE, ONGEDEFINIEERD: select sources having none of their variables (involved in a data quality status check) reporting a data quality status

Example

DataQuality{GOOD} : select all sources reporting a good data quality
DataQuality{GOOD} and Name like MyName : select all sources satisfying Name like MyName and reporting a good data quality

Note

This is only meaningful when searching for Sources.

SourceType{partialname }, MeterType{partialname}, TypeSource{ partialname}, TypeEquipement{ partialname}, BronSoort{partialname}

Searches for sources having a source type whose name partially matches partialname.

Example

SourceType{ABC} : select all sources having a source type matching *ABC* (e.g. _ABC, ABC, ABCD)
SourceType{ABC} and Name like MyName : select all sources satisfying Name like MyName and having a source type matching *ABC* (e.g. _ABC, ABC, ABCD)

Note

This is only meaningful when searching for Sources.

GatewayType{partialname}, TypePasserelle{partialname}, ToegangspoortSoort{ partialname}

Searches for sources having a gateway type whose name partially matches partialname.

Example

GatewayType{ABC} : select all sources having a gateway type matching *ABC* (e.g. _ABC, ABC, ABCD)
GatewayType {ABC} and Name like MyName : select all sources satisfying Name like MyName and having a gateway type matching *ABC* (e.g. _ABC, ABC, ABCD)

Note

This is only meaningful when searching for Sources

Entities or values with white spaces

White spaces or tabs are used by the query tokenizer as token separator, meaning that the query My property = some value will produce a parsing error because the tokenizer will produce [my, property, =, some, value], which is an invalid parsing tree [my, property=some, value]. To tell the tokenizer to treat multiple tokens as a single one, enclose these tokens by single quote: ' My property' = 'some value' which produces the expected parsing tree [my property = some value].

Combine search criteria's

Binary operators

Note

Operator names are case insensitive

Operator	Description
AND, ET, EN, &	Left and right expressions must evaluate to trueExample: Name like xyz AND Name !like a_xyz
OR, OU, OF,	Left or right expressions must evaluate to trueExample: Name like xyz OR Name !like abc

Operators precedence

By default, OR operators have precedence to AND operators. The precedence can be modified by surrounding relational expressions with parentheses.

Example: (Name = ABC OR Name = def) AND Name = ghi. The default precedence is equivalent to Name = ABC OR (Name = def AND Name = ghi)

Implied AND

When two or more comparison expressions are separated by white chars, the white chars are considered as an implied AND.

Example: Name = abc MeterNumber = efg is equivalent to Name = abc AND MeterNumber = efg

Legacy search fallback

The query syntax described in this document applies to API fields siteFilter.customFilter and sourceFilter.customFilter. There are also siteFilter.searchText and sourceFilter.searchText search filters that were formerly used to perform a full text search on entity properties. They are now legacy but kept for backward compatibility. The new syntax parser reverts to full text search (compatible with the legacy full text search) in the following conditions:

The search query contains a single term
- Example: acme (matches e.g Name = acme, EanNumber = Acmé, …)
The search query contains multiple terms not involved in any query expression
- Example : acme1 acm2 (matches e.g Name = NameAcme1 OR Name = 'Some acme2' OR EanNumber = acme1, …)
The search query contains multiple terms surrounded by double quotes not involved in any query expression. The casing does not matters but the word order matters.
- Example: 'acme1 acm2' (matches e.g Name = 'This is Acme1 acme2' but does not match Name = 'This is acme2 Acme1'…)

Syntax terms types

String

Sting values are expressed either without quotes or surrounded by single quotes.

ThisIsAString
'This is a string with spaces'
'ThisIsAString'
'123'
This is not a string but isolated terms

Integer

Integers range from -2147483648 to 2147483647. The sign identifier is optional for positive numbers.

-123
0
123
+123

Double

Doubles range from -1.79769313486232E+308 to 1.79769313486232E+308. The sign identifier is optional for positive numbers. Numbers can also be expressed in Exponential form.

The decimal separator is the dot '.' and no comma is allowed neither for decimal separator nor for thousands separator.

1.23
-0.001
1.2E3
-3.4E-3

Date

Date range follows the Unix epoch range. They support the following formats:

yyyy
M-yyyy
d-M-yyyy
d-M-yyyy H
d-M-yyyy H:m
d-M-yyyy H:m:s
M/yyyy
d/M/yyyy
d/M/yyyy H
d/M/yyyy H:m
dd/M/yyyy H:m:s
yyyy-M
yyyy-M-d
yyyy-M-d H
yyyy-M-d H:m
yyyy-M-d H:m:s
yyyy-M-d H:m:s.fff
yyyy-M-d H:m:s.fffZ
yyyy-M-dTH:m:s.fffZ
yyyy-M-dTH:m:s.fff

Note

Information the user should notice even if skimmingDate expression MUST be surrounded by single quotes ('2017-08-06T00:00:00.000Z')

Terminals

Terminal	Description	Sample
and, &, et, en	AND	PostalCode = 1340 and City = 'Louvain-La-Neuve'
or, ou, of	OR	PostalCode = 1340 of City = 'Louvain-La-Neuve'
=	EQUAL	City = 'Here'
!=, <>	NOT EQUAL	Number <> 0
<	LOWER THAN	Number < 0
<=	LOWER THAN OR EQUAL	Number <= 0
>	GREATER THAN	Number > 0
>=	GREATER THAN OR EQUAL	Number >= 0
~, like	LIKE	City like 'ru' (matches 'Brussels', 'Prum')
!~, not like, !like	NOT LIKE	City not like 'ru'
endswith, finitpar, eindigtmet	ENDS WITH	City finitpar 'els'
!endswith !finitpar, !eindigtmet	DO NOT END WITH	City !endswith 'els'
startswith, commencepar, beginmet	STARTS WITH	City startswith 'Bru'
!startswith, !commencepar, !beginmet	DO NOT START WITH	City not startswith 'Bru'
()	Change operator precedence	A=1 and (B=2 or C=3)
.	Compound identifier separator	Form.Group.'Field name'
Identifier{}	Specify a foreign entity	Site{name = 'MySite'}
empty, leeg, vide	Constant	'A form property' = empty
Identifier	Identifier	Name
'Identifier with space'	Identifier	'A Name'
'legacy Identifier with space'	Identifier	'A Name'
[]	Restrict the search to system properties only (not part of a form)	[Name] = xyz
ImpliedAnd (whitespace)	Whitespace between expressions is an implied AND	A=1 B=2 C=3

BNF grammar

BinaryExpression

BinaryExpression -> SelfEntityExpression BinaryOp SelfEntityExpression
BinaryExpression -> ForeignEntityExpression BinaryOp SelfEntityExpression
BinaryExpression -> SelfEntityExpression BinaryOp ForeignEntityExpression
BinaryExpression -> ForeignEntityExpression BinaryOp ForeignEntityExpression

BinaryOp

BinaryOp -> ImpliedAndTerminal
BinaryOp -> and
BinaryOp -> &
BinaryOp -> et
BinaryOp -> en
BinaryOp -> or
BinaryOp -> |
BinaryOp -> ou
BinaryOp -> of

ComparisonExpression

ComparisonExpression -> LeftSideCompare StrictCompareOp StrictRightSideCompare
ComparisonExpression -> LeftSideCompare PartialCompareOp PartialRightSideCompare

DottedExpression

DottedExpression -> IdentifierExpression . IdentifierExpression
DottedExpression -> PropertyStringExpression . IdentifierExpression
DottedExpression -> IdentifierExpression . PropertyStringExpression
DottedExpression -> IdentifierExpression . DottedExpression
DottedExpression -> PropertyStringExpression . PropertyStringExpression
DottedExpression -> PropertyStringExpression . DottedExpression

ForeignEntityExpression

ForeignEntityExpression -> IdentifierExpression { ForeignEntityExpression }

ForeignEntityExpression

ForeignEntityExpression -> SelfEntityExpression
ForeignEntityExpression -> Constant
ForeignEntityExpression -> IdentifierExpression

LeftSideCompare

LeftSideCompare -> IdentifierExpression
LeftSideCompare -> PropertyStringExpression
LeftSideCompare -> SystemPropertyExpression
LeftSideCompare -> DottedExpression

ParenthesizedExpression

ParenthesizedExpression -> ( SelfEntityExpression )
ParenthesizedExpression -> ( ForeignEntityExpression )

PartialCompareOp

PartialCompareOp -> ~
PartialCompareOp -> !~
PartialCompareOp -> like
PartialCompareOp -> comme
PartialCompareOp -> zoals
PartialCompareOp -> !like
PartialCompareOp -> !comme
PartialCompareOp -> !zoals
PartialCompareOp -> startswith
PartialCompareOp -> commencepar
PartialCompareOp -> beginmet
PartialCompareOp -> !startswith
PartialCompareOp -> !commencepar
PartialCompareOp -> !beginmet
PartialCompareOp -> endswith
PartialCompareOp -> finitpar
PartialCompareOp -> eindigtmet
PartialCompareOp -> !endswith
PartialCompareOp -> !finitpar
PartialCompareOp -> !eindigtmet

PartialRightSideCompare

PartialRightSideCompare -> IdentifierExpression
PartialRightSideCompare -> NumberExpression
PartialRightSideCompare -> StringExpression

SelfEntityExpression

SelfEntityExpression -> BinaryExpression
SelfEntityExpression -> ParenthesizedExpression
SelfEntityExpression -> ComparisonExpression

SingleIdentifierExpression

SingleIdentifierExpression -> IdentifierExpression
SingleIdentifierExpression -> DoubleQuotedStringExpression
SingleIdentifierExpression -> SingleIdentifierExpression ImpliedAndTerminal SingleIdentifierExpression

StrictCompareOp

StrictCompareOp -> =
StrictCompareOp -> >
StrictCompareOp -> <
StrictCompareOp -> <=
StrictCompareOp -> >=
StrictCompareOp -> <>
StrictCompareOp -> !=

StrictRightSideCompare

StrictRightSideCompare -> IdentifierExpression
StrictRightSideCompare -> Constant
StrictRightSideCompare -> NumberExpression
StrictRightSideCompare -> DottedExpression
StrictRightSideCompare -> StringExpression

SystemPropertyExpression

SystemPropertyExpression -> [IdentifierExpression]
SystemPropertyExpression -> [NumberExpression]
SystemPropertyExpression -> [PropertyStringExpression]

Relaxed type interpretation

The grammar parser will always try to interpret values in as much type as it can. This means that you can specify either an integer of a string to compare either a string or an integer.

A=1980 is then interpreted as ( A=1980 or A='1980' or A=1980.0 or A='1980-01-01T00:00:00.000' )

API call

private static async Task<Source[]> GetSources(HttpClient client, int? siteId)
{
   var siteFilter = siteId == null ? "" : "$&siteId={siteId}";
   var customQuery = "PostalCode=1000";
   var encodedQuery = HttpUtility.UrlEncode(customQuery);
   var response = await client.GetAsync($"{DataHubApi}sources?displayLevel=site{siteFilter}&customFilter={encodedQuery}");
   response.EnsureSuccessStatusCode();
   return await response.Content.ReadAsAsync<Source[]>();
}

Documentation Center

Custom filter query syntax

Context

Queries

Relational operators

Note

Find entities by value

identifier relational_operator value

Find system entities by value

[identifier] relational_operator value

Find form entities by value

form_identifier.group_identifier.field_identifier relational_operator value

Note

Find entities with empty properties

identifier = empty, identifier != empty, identifier <> empty

Note

Find entities based on a foreign entity

Site{expression}, Lieu{expression}, Locatie{expression}

Note

Source{expression}, Meter{expression}, Equipement{expression}, Bron{expression}

Note

DataQuality{quality_value}, Qualite{ quality_value }, Kwaliteit{ quality_value }

Note

SourceType{partialname }, MeterType{partialname}, TypeSource{ partialname}, TypeEquipement{ partialname}, BronSoort{partialname}

Note

GatewayType{partialname}, TypePasserelle{partialname}, ToegangspoortSoort{ partialname}

Note

Entities or values with white spaces

Combine search criteria's

Binary operators

Note

Operators precedence

Implied AND

Legacy search fallback

Syntax terms types

String

Integer

Double

Date

Note

Terminals

BNF grammar

BinaryExpression

BinaryOp

ComparisonExpression

DottedExpression

ForeignEntityExpression

ForeignEntityExpression

LeftSideCompare

ParenthesizedExpression

PartialCompareOp

PartialRightSideCompare

SelfEntityExpression

SingleIdentifierExpression

StrictCompareOp

StrictRightSideCompare

SystemPropertyExpression

Relaxed type interpretation

API call

Developer Center

Follow us