Filter Expressions

Filter expressions in STTP are used to select desired signals for subscription or to reduce available meta-data down to a desired subset. Filtering syntax is similar to Structured Query Language (SQL), but does not implement the full SQL language.

Filter expressions operate against in-memory data set, not a backend database. The filtering syntax used in conjunction with a data set is designed for read-only operations and exposes no update functionality. Because of this, filter operations are not subject to SQL injection attacks or other security concerns typically associated with SQL implementations.

STTP data publishers need to define a data set consisting of a collection of data tables representing the primary meta-data from locally defined configurations that contain information about the time-series style data to be published. At a minimum this meta-data should define a Guid based identifier for each measurement to be published as well as an associated source, i.e., a device, that produces the measurement.

The STTP data publisher API defines functions to help create needed meta-data, see samples and specific example

Filtering Syntax

FILTER [TOP n] <TableName> WHERE <Expression> [ORDER BY <SortField> [ASC|DESC]]

Filter expressions in STTP are parsed using ANTLR. For complete syntax description, see full ANTLR grammar: FilterExpressionSyntax.g4

Available Options and Clauses

Keyword	Example	Description	Required?
FILTER	See examples below	Keyword that signifies a filter expression follows*	Yes
TOP n	TOP 100	Selects only the first n number of items	No
WHERE <Expression>	WHERE SignalType='FREQ'	Criteria based expression, in SQL syntax, used to filter rows	Yes
ORDER BY <ColumnName>	ORDER BY SignalType	Expression specifying column names and sort directions	No

The keyword FILTER is used instead of the standard SQL SELECT keyword to reinforce the notion that the expression that follows is special purposed and not standard SQL.

Direct Signal Identification

Filtering syntax also supports the direct specification of desired signals as semi-colon separated measurement references in a variety of forms, e.g., measurement key identifiers: PPA:4; PPA:2 - formatted as {instance}:{id}, unique Guid-based signal identifiers: 538A47B0-F10B-4143-9A0A-0DBC4FFEF1E8; ‘06d039f6-e5e9-4e37-85fc-52a125c67a06’; {E4BBFE6A-35BD-4E5B-92C9-11FF913E7877} optionally surrounded by single quotes or braces, or point tag name identifiers: “GPA_TESTDEVICE:FREQ”; “GPA_TESTDEVICE:FLAG” where each point tag name is in double quotes.

Examples

Example filter expression to select measurements with the company of GPA and type of Frequency (FREQ) or Voltage Magnitude (VPHM):

    FILTER ActiveMeasurements WHERE Company='GPA' AND SignalType IN ('FREQ', 'VPHM') ORDER BY Device DESC

Example filter expression to select first 20 measurements of type Statistic (STAT):

    FILTER TOP 20 ActiveMeasurements WHERE SignalType = 'STAT'

Example filter to only select Current Angle (IPHA) and Voltage Angle (VPHA) for Positive Sequence (+) measurements.

    FILTER ActiveMeasurements WHERE SignalType LILE '%PHA' AND Phase='+' ORDER BY PhasorID

Example filter combining both filter expressions and directly specified tags:

PPA:15; STAT:20; PPA:8; {eecbda2f-fe76-4504-b031-7f5518c7046c};
FILTER ActiveMeasurements WHERE SignalType IN ('IPHA', 'VPHA'); 9d0423c0-2349-4a38-85d5-b6e81735eb48;
FILTER TOP 3 ActiveMeasurements WHERE SignalType = 'FREQ' ORDER BY Device; "GPA_TESTDEVICE:FREQ"

Filter Expression Operators

Any operators that consist of letters, e.g., OR, are not case sensitive, so OR, or and Or are all equivalent.

Unary Operators

Type	Symbol
Negative	-
Positive	+
Not	NOT or ! or ~

Comparison Operators

Type	Symbol
Less Than	<
Less Than or Equal	<=
Greater Than	>
Greater Than or Equal	>=
Equal	= or == or ===
Not Equal	!= or <> or !==

Logical Operators

Type	Symbol
And	AND or &&
Or	OR or ||

Bitwise Operators

Type	Symbol
Bit Shift Left	<<
Bit Shift Right	>>
Bitwise And	&
Bitwise Or	|
Exclusive Or	XOR or ^

Math Operators

Type	Symbol
Multiply	*
Divide	/
Add	+
Subtract	-
Modulus	%

Other Operators

Symbol	Syntax
LIKE	<ColumnName> LIKE 'pattern'
IN	<ColumnName> IN (expression1, ..., expression_n)

The pattern for the LIKE operator uses wildcards identified as a percent sign (%), or an asterisk (*), allowed at the beginning or the end of the pattern, which represents zero, one, or multiple characters in the source column value. Wildcards in the middle of the string are not supported.

Case Sensitive String Comparisons

Unless otherwise specified, comparison of string values in filter expressions is not case sensitive. To specify a case sensitive comparison, use one of the following expression options:

Case Sensitive LIKE Expression

FILTER <TableName> WHERE <ColumnName> [NOT] LIKE [===|BINARY] 'pattern'

Example:

FILTER ActiveMeasurements WHERE Device LIKE BINARY 'SHELBY%'

Case Sensitive IN Expression

FILTER <TableName> WHERE expression [NOT] <ColumnName> IN [===|BINARY] (expression1, ..., expression_n )

Example:

FILTER ActiveMeasurements WHERE NOT SignalType IN ===('IPHM', 'VPHM')

Case Sensitive ORDER BY Expression

FILTER <TableName> WHERE expression ORDER BY [===|BINARY] <ColumnName> [ASC|DESC]

Example:

FILTER TOP 5 ActiveMeasurements ORDER BY Device, === PointTag DESC

Case Sensitive Comparison Operators

When expressions are strings, or evaluated as strings, the following operators perform a case sensitive compare:

expression1 === expression2   // Case sensitive equals comparison
expression1 !== expression2   // Case sensitive not equals comparison

Example:

FILTER ActiveMeasurements WHERE Device === 'SHELBY'

Filter Expression Functions

Function names are not case sensitive, so ABS, abs and Abs are all equivalent.

Function	Arguments	Description
ABS	expression	Returns the absolute value of the specified numeric expression.
CEILING	expression	Returns the smallest integer that is greater than, or equal to, the specified numeric expression.
COALESCE	expression1, …, expression_n	Returns the first non-null value in expression list.
CONVERT	expression, type	Returns expression converted to the specified type. type is one of boolean (or bool), int32, uint, int64 (or int), decimal, single (or float), double, string, UUID (or GUID),or datetime. type is not case sensitive.
CONTAINS	source, test, [ignorecase]	Returns boolean flag that determines if source string contains test string. ignorecase is an optional boolean flag, defaults to false, to determine if string comparison is case sensitive.
DATEADD	source, value, interval	Returns dateTime of integer value at specified interval added to source dateTime value. interval is one of Year, Month, DayOfYear, Day, Week, WeekDay, Hour, Minute, Second, or Millisecond. interval is not case sensitive.
DATEDIFF	left, right, interval	Returns the integer difference between left and right dateTime value at specified interval. interval is one of Year, Month, DayOfYear, Day, Week, WeekDay, Hour, Minute, Second, or Millisecond. interval is not case sensitive.
DATEPART	source, interval	Returns specified integer interval of source dateTime value. interval is one of Year, Month, DayOfYear, Day, Week, WeekDay, Hour, Minute, Second, or Millisecond. interval is not case sensitive.
ENDSWITH	source, test, [ignorecase]	Returns boolean flag that determines if source string ends with test string. ignorecase is an optional boolean flag, defaults to false, to determine if string comparison is case sensitive.
FLOOR	expression	Returns the largest integer value that is smaller than, or equal to, the specified numeric expression.
IIF	expression, leftValue, rightValue	Returns leftValue if result of expression is true, else returns rightValue.
INDEXOF	source, test, [ignorecase]	Returns zero-based integer index of first occurrence of test string in source string, or -1 if not found. ignorecase is an optional boolean flag, defaults to false, to determine if string comparison is case sensitive.
ISDATE	expression	Returns boolean flag that determines if expression is a dateTime or can be parsed as one.
ISINTEGER	expression	Returns boolean flag that determines if expression is an integer value or can be parsed as one.
ISUUID	expression	Returns boolean flag that determines if expression is a UUID value or can be parsed as one.
ISNULL	expression	Returns boolean flag that determines if expression is null.
ISNUMERIC	expression	Returns boolean flag that determines if expression is a numeric value or can be parsed as one.
LASTINDEXOF	source, test, [ignorecase]	Returns zero-based integer index of last occurrence of test string in source string, or -1 if not found. ignorecase is an optional boolean flag, defaults to false, to determine if string comparison is case sensitive.
LEN	expression	Returns integer length of expression interpreted as a string.
LOWER	expression	Returns lower-case representation of expression interpreted as a string.
MAXOF	expression1, …, expression_n	Returns value in expression list with maximum value.
MINOF	expression1, …, expression_n	Returns value in expression list with minimum value.
NOW		Returns dateTime value representing the current local system time.
NTHINDEXOF	source, test, index, [ignorecase]	Returns zero-based integer index of the Nth, represented by integer index value, occurrence of test string in source string, or -1 if not found. ignorecase is an optional boolean flag, defaults to false, to determine if string comparison is case sensitive.
POWER	expression, exponent	Returns the value of specified numeric expression raised to the power of specified numeric exponent.
REGEXMATCH	regex, test	Returns boolean flag that determines if test, interpreted as a string, is a match for specified regex string-based regular expression.
REGEXVAL	regex, test	Returns string value from test, interpreted as a string, that is matched by specified regex string-based regular expression.
REPLACE	source, test, replace, [ignorecase]	Returns string where all instances of test found in source are replaced with replace value - all parameters interpreted as strings. ignorecase is an optional boolean flag, defaults to false, to determine if string comparison is case sensitive.
REVERSE	expression	Returns string where all characters in expression, interpreted as a string, are reversed.
ROUND	expression	Returns the nearest integer value to the specified numeric expression.
SPLIT	source, delimiter, index, [ignorecase]	Returns zero-based Nth, represented by index, string value in source string split by delimiter string, or null if out of range. ignorecase is an optional boolean flag, defaults to false, to determine if string comparison is case sensitive.
SQRT	expression	Returns the square root of the specified numeric expression.
STARTSWITH	source, test, [ignorecase]	Returns boolean flag that determines if source string starts with test string. ignorecase is an optional boolean flag, defaults to false, to determine if string comparison is case sensitive.
STRCOUNT	source, test, [ignorecase]	Returns integer count of occurrences of test string in source string. ignorecase is an optional boolean flag, defaults to false, to determine if string comparison is case sensitive.
STRCMP	left, right, [ignorecase]	Returns integer -1 if left string is less-than right string, 1 if left string is greater-than right string, or 0 if left string equals right string. ignorecase is an optional boolean flag, defaults to false, to determine if string comparison is case sensitive.
SUBSTR	source, index, [length]	Returns string from portion of source, interpreted as a string, starting at integer index. If length is specified, this will be the maximum number of characters returned; otherwise, remaining characters in source string will be returned.
TRIM	expression	Returns string with white-space removed from the beginning and end of expression interpreted as a string.
TRIMLEFT	expression	Returns string with white-space removed from the beginning of expression interpreted as a string.
TRIMRIGHT	expression	Returns string with white-space removed the end of expression interpreted as a string.
UPPER	expression	Returns upper-case representation of expression interpreted as a string.
UTCNOW		Returns dateTime value representing the current UTC system time.

Signal Selection Meta-data Table Definitions

Data publishers can define multiple tables that represent sets of measurements available for filtering desired signals, e.g., AllMeasurements or LocalMeasurements. At a minimum a signal selection table must define a SignalD field of type Guid - all other fields are considered optional. However, without a point tag name or description the measurement may be of little use unless other meta-data is exchanged out-of-band with STTP.

Signal selection tables should represent a simple flattened “view” of available meta-data with as many fields as needed to be useful for measurement selection operations. See usage of ActiveMeasurements in examples.

ActiveMeasurements

The ActiveMeasurements table is always expected to be defined. This table represents all measurements considered active and available for subscription. If a data publisher is controlling access to measurements on a per-subscriber basis, this table should only include the measurements the subscriber is allowed to request for subscription.

Typically the data in the ActiveMeasurements table is derived from the conflation of information already defined in other available meta-data condensed to a single table to make filter expressions more productive.

Common fields for the ActiveMeasurements table are defined below. Note that some of the fields are specific to the electric power industry and may not be applicable for other industry implementations and consequently unavailable.

The STTP data publisher API will automatically generate the ActiveMeasurements table when primary meta-data tables are defined, see the DefineMetadata function.

Column Name	Data Type	Description
ID	string	A measurement key identifier formatted as {instance}:{id}
SignalID	Guid	Unique identifier for the measured value
PointTag	string	Unique alpha-numeric identifier for the measured value
AlternateTag	string	Secondary alpha-numeric identifier for the measured value
SignalReference	string	Alpha-numeric reference to original signal source, e.g., location in source protocol
Device	string	Alpha-numeric device acronym that is the source of the measurement
FramesPerSecond	int	Expected data rate, in received samples per second, of measurement
Protocol	string	Source protocol that generated measurement
SignalType	string	Signal type acronym of measurement
EngineeringUnits	string	Engineering units of measurement
PhasorID	int	ID of associated phasor meta-data record
PhasorType	string	When measurement is a phasor, type of phasor: voltage (V) or current (I)
Phase	string	When measurement is a phasor, phase e.g.: (A), (B), (C), (+), (-), etc.
Adder	double	Recommended additive linear adjustment of value to be applied
Multiplier	double	Recommended multiplicative linear adjustment of value to be applied
Company	string	Acronym of company that is publishing the measurement
Longitude	decimal	Longitude of device location publishing the measurement
Latitude	decimal	Latitude of the device location publishing the measurement
Description	string	Description of the measurement
UpdatedOn	dateTime	Timestamp of last update of measurement meta-data

Primary Meta-data Table Definitions

STTP meta-data is designed around the notion of a data set. Meta-data represented by a data set allows for rich and extensible information description.

Outside the expected ActiveMeasurements signal selection meta-data table definition, no other meta-data tables are required to be defined. However, to make data exchange useful for industry specific STTP implementations, a common set of meta-data should be defined.

The STTP data publisher API currently defines three primary data tables to define enough useful meta-data to allow a measurement data subscription to be converted into another protocol, e.g., IEEE C37.118. When these tables are defined, the data publisher API will auto-generate the ActiveMeasurements table from the provided data.

DeviceDetail

This meta-data table contains details about the devices that are the sources of available measurements. By convention, measurements that are not associated with a device are not sent in meta-data exchanges.

Column Name	Data Type	Description
UniqueID	Guid	Unique identifier for the device
OriginalSource	string	If device was proxied through another protocol, original source
IsConcentrator	boolean	Flag that determines if device is a container for other devices
Acronym	string	Alpha-numeric device acronym
Name	string	Free form device name
AccessID	int	ID code associated with device, if any
ParentAcronym	string	If device is a child of another device, acronym of parent device
FramesPerSecond	int	Expected data rate, in received samples per second, for device measurements
CompanyAcronym	string	Company that owns device
VendorAcronym	string	Vendor that manufactures device
VendorDeviceName	string	Vendor device name and/or model information
Longitude	decimal	Longitude of device location
Latitude	decimal	Latitude of the device location
InterconnectionName	string	Eastern, Western, etc.
ContactList	string	Names / e-mail addresses of parties responsible for device
Enabled	boolean	Flag that determines if device is currently enabled
UpdatedOn	dateTime	Timestamp of last update of device meta-data

MeasurementDetail

This meta-data table contains details about the measurements available for subscription.

Column Name	Data Type	Description
DeviceAcronym	string	Alpha-numeric device acronym that is the source of the measurement
ID	string	A measurement key identifier formatted as {instance}:{id}
SignalID	Guid	Unique identifier for the measured value
PointTag	string	Unique alpha-numeric identifier for the measured value
SignalReference	string	Alpha-numeric reference to original signal source, e.g., location in source protocol
SignalAcronym	string	Type of signal, e.g., FREQ for frequency
PhasorSourceIndex	int	Index of phasor source if measurement type is a phasor
Description	string	Description of the measurement
Enabled	boolean	Flag that determines if measurement is currently enabled
UpdatedOn	dateTime	Timestamp of last update of measurement meta-data

PhasorDetail

This meta-data table, specific to data exchanges containing electrical measurements with phasor values, contains details about the phasors whose vector magnitude and angle component measurements are available for subscription.

Column Name	Data Type	Description
ID	int	Numeric auto-incrementing identifier
DeviceAcronym	string	Alpha-numeric device acronym that is the source of the phasor
Label	string	Free form phasor label
Type	string	Type of phasor, i.e.: voltage (V) or current (I)
Phase	string	Phase of phasor, e.g.: (A), (B), (C), (+), (-), etc.
DestinationPhasorID	int	Associated phasor, e.g., typical voltage for current
SourceIndex	int	Index of phasor as defined in source protocol
UpdatedOn	dateTime	Timestamp of last update of DestinationPhasorID meta-data