Filter Functions

Filter functions provided by the SanteSuite community are summarized on this page.

Core HDSI Filters

Age

The age filter is used to express a filter based on the "age" of a timestamp at a particular date. Age can be passed with no parameters (indicating age at current date) or can be used with a parameter (indicating age at specified date).

:(age)value
:(age|yyyy-MM-dd)value

Where value is an ISO8601 duration format.

Parameter	Opt	Description
dateOfAge	Optional	The date on which the age value should be evaluated.
(return)		The date difference between the filter field and the input parameter in ISO8601 duration.

SQL Translations

PostgreSQL	Supported (since 2.1)
FirebirdSQL	Supported (since 2.1)
SQLite	Not Supported (see date_diff)

PostgreSQL

WHERE GREATEST(column::TIMESTAMP - COALESCE(@otherDate::TIMESTAMP, CURRENT_TIMESTAMP), 
      COALESCE(@otherDate::TIMESTAMP, CURRENT_TIMESTAMP) - column::TIMESTAMP) <operator> @value::INTERVAL

FirebirdSQL

WHERE ABS(DATEDIFF(millisecond, CAST(column AS TIMESTAMP), 
         COLESACE(CAST(@otherDate AS TIMESTAMP), CURRENT_TIMESTAMP)) <operator> @value

Examples

// Under 1 year at current date
dateOfBirth=:(age)<P1Y
// Over 2 years old on Jan 1 2022
dateOfBirth=:(age|2022-01-01)>P2Y

Date Difference

The date difference function is enabled on PostgreSQL and FirebirdSQL ORM providers and require no additional configuration. The value is any HDSI operator and an ISO8601 duration.

:(date_diff|otherDate)value

Parameter	Opt	Description
@otherDate	Required	The other date to compare to. This duration is translated to TimeSpan and the total seconds are used for comparison.
(return)		The difference between the property and the otherDate in ISO8601 duration

SQL Translations

PostgreSQL	Supported (since 2.0)
FirebirdSQL	Supported (since 2.0)
SQLite	Supported (since 2.0)

PostgreSQL

WHERE GREATEST(column::TIMESTAMP - @otherDate::TIMESTAMP, 
      @otherDate::TIMESTAMP - column::TIMESTAMP) <operator> @value::INTERVAL

FirebirdSQL

WHERE ABS(DATEDIFF(millisecond, CAST(column AS TIMESTAMP), 
         CAST(@otherDate AS TIMESTAMP)) <operator> @value

SQLite

WHERE ABS(column - @otherDate.Ticks) <operator> @value.Ticks

Examples

// Born within 3 years of 1990
dateOfBirth=:(date_diff|1990)<3y

Substring

Extracts a portion of a string and matches it with the provided value.

:(substr|start,[length])value

Parameter	Opt	Description
start	Required	The starting position in the string
length	Optional	The length to extract
(return)		The extracted part of the string

SQL Translations

PostgreSQL	Supported (since 2.0)
FirebirdSQL	Supported (since 2.0)
SQLite	Supported (since 2.0)

PostgreSQL & FirebirdSQL

WHERE SUBSTRING(column FROM @start FOR @length) <operator>
       SUBSTRING(@value FROM @start FOR @length)

SQLite

WHERE substr(column, @start, @length) <operator> substr(@value, @start, @length)

Examples

// Identifier where the first 5 digits match 12345
identifier.value=:(substr|0,5)12345

Date Truncate

Truncates a full DateTime or DateTimeOffset object to only the precision specified.

:(date_trunc|[yMd])value

Parameter	Opt	Description
precision	Required	y = Year portion M = Year + Month d = Year + Month + Day
(return)		A DateTime or DateTimeOffset truncated to only the portion specified.

SQL Translations

PostgreSQL	Supported (since 2.2)
FirebirdSQL	Supported (since 2.2)
SQLite	Not Supported

PostgreSQL / FirebirdSQL

WHERE column BETWEEN @minDate AND @maxDate

@minDate and @maxDate are computed in .NET based on precision where:

Precision	@minDate	@maxDate
Year	new DateTime(value.Year, 01, 01)	new DateTime(value.Year, 12, 31)
Month	new DateTime(value.Year, value.Month, 01)	new DateTime(value.Year, value.Month, DateTime.DaysInMonth(value.Month)
Day	value.Date	value.Date

Hex Decode

Decodes a hex encoded value for query as the raw string in the database. This is useful for querying by security token values, or other data where the data in the database is stored as a byte / blob and represented in the API as a hex encoded string.

:(hexdecode)AFB4FE

Byte Value

Whenever a plain text string is presented on the API but stored as raw byte array in the database (such as Extended Data using the StringExtensionHandler) the byte value may be used.

For example, to query a string extension containing the value bar the following HDSI query can be used:

?extension[http://example.com/foo].value=:(tobytes)bar

Base 64 Decode

When querying an extended value (see Extended Data) using the API serialized base-64 string provided - the base64decode operation can be used:

?name.component.value=:(base64decode)AQ==

No Case

The nocase filter performs a case-insensitive match. For example, to ignore the case of a query on a family name SMITH:

?name.component[Family].value=:(nocase)SMITH

Trim

The trim filter will trim all spaces surrounding a value.

?name.component[Family].value=:(nocase)%20%20SMITH%20

Freetext

The freetext filter will perform a match on the specified object using whatever underlying freetext or fulltext search engine the persistence layer is configured with. Free text is always applied against the object using $self .

?$self@Patient=:(freetext)+JOHN +SMITH -SALLY

Get Claim

The get claim query filter extension method is a special implementation for security objects. The claim value is filtered on a SecurityUser, SecurityDevice, or SecurityApplication's claim instance. This is executed against $self

?$self@SecurityUser=:(getClaim|notBefore)>2025-06-04

SanteMatch Filters

Approximate Match

Approximate matching is enabled when the SanteDB matcher plugin is enabled in the configuration for the dCDR or iCDR. The approximate matching function will use a combination of pattern, phonetic, and string difference functions to determine matching based on the configuration of the server running the query.

:(approx|otherString)

Parameter	Opt	Description
otherString	Required	The other string to compare approximation to
(return)		True if the server determines the property is approximately the same as otherString

SQL Translations / Support

PostgreSQL	Supported (since 2.0)
FirebirdSQL	Supported (since 2.0)
SQLite	Not Supported

Examples

Filter for a name which sounds like, is about the same as, or minor typo's from JIHN (i.e. match JOHN, JOHNNY, JON, etc.).

name.component.value=:(approx|JIHN)

Sounds Like

Uses the configured phonetic algorithm to determine whether the supplied string sounds like the stored property value. The algorithm, if not specified by the implementer, is the discretion of the implementer of the server plugin.

:(soundslike|otherString,[algorithm])

Parameter	Opt	Description
otherString	Required	The other string to compare
algorithm	Optional	Dictates the algorithm to use (soundex, metaphone, dmetaphone)
(return)		True if the server determines the property sounds like otherString

SQL Translations / Support

PostgreSQL	Supported (since 2.0)
FirebirdSQL	Not Supported
SQLite	Supported (since 2.0 - requires SQLite Soundex option)

Examples

To filter for patients who have a name which sounds like Tyler (i.e. match Tyler, Tiler, etc.)

name.component.value=:(soundexlike|TYLER)

Phonetic Difference

The phonetic difference function is used to compare the difference in phonetic codes between two values. This function by default uses the SOUNDEX algorithm and then performs a LEVENSHTEIN function against the result.

:(phonetic_diff|otherString,[algorithm])value

Parameter	Opt	Description
otherString	Required	The other string to compare
algorithm	Optional	Dictates the algorithm to use (soundex, metaphone, dmetaphone)
(return)		The difference (0 - 4) in soundex codes

SQL Translations / Support

PostgreSQL	Supported (since 2.1)
FirebirdSQL	Not Supported
SQLite	Not Supported

Soundex Comparison

The soundex comparison is used to compare the difference in SOUNDEX codes of each input.

:(soundex)OTHER

SQL Translations / Support

PostgreSQL	Supported (since 2.0)
FirebirdSQL	Not Supported
SQLite	Supported (since 2.0 - requires SQLite Soundex option)

Metaphone Comparison

The metaphone comparison is used to compare the values based on their metaphone code. Metaphone filter takes an optional length specifier.

:(metaphone)OTHER
:(metaphone|3)OTHER

SQL Translations / Support

PostgreSQL	Supported (since 2.0)
FirebirdSQL	Not Supported
SQLite	Supported (since 2.0 - requires SQLite spellfix plugin)

Double Metaphone Comparison

The double metaphone comparison is used to compare values based on the double metaphone code.

:(dmetaphone)OTHER

SQL Translations / Support

PostgreSQL	Supported (since 2.0)
FirebirdSQL	Not Supported
SQLite	Not Supported

Levenshtein Difference

The levenshtein difference is used to compute the difference in edit distance between the source and input.

:(levenshtein|OTHER)<3

Using the levenshtein function has a performance penalty in that the database tables storing the values (identifiers, addresses, etc.) needs to be sequentially scanned. If you can, consider using similarity_lev which can uses PostgreSQL's trigram index (see similarity extension).

SQL Translations / Support

PostgreSQL	Supported (since 2.0)
FirebirdSQL	Not Supported
SQLite	Supported (since 2.0 - requires SQLite spellfix plugin)

Similarity

The similarity function will use the database's string matching similarity functionality to perform an indexed match (in PostgreSQL the similarity operator used). For example, to compare similarity of given names > 0.8 from SMITH

name.component[Family].value=:(similarity|SMITH)>0.8

When using PostgreSQL the :(similarity) filter function will be translated into an optimized lookup of column % 'SMITH' AND similarity(column, 'SMITH') > 0.8 . It is therefore important to properly set the default (used by the % operator) via:

ALTER DATABASE santedb SET pg_trgm.word_similarity_threshold = 0.6;

SQL Translations / Support

PostgreSQL	Supported (since 2.2)
FirebirdSQL	Not Supported
SQLite	Not Supported

Similarity + Levenshtein

The similarity_lev filter acts similar to the similarity in that it uses the underlying database technology's GIN indexing to perform a similarity, however the final result is run through the levenshtein function.

identifier[SSN].value=:(similarity_lev|304-304-3049)<3

Would be queried in PostgreSQL as:

WHERE id_val % '304-304-3049' AND levenshtein(id_val, '304-304-3049') < 3

This method should be used over similarity when:

• The strings being compared require an exact number of modifications to pass the filter (like identifiers accounting for type-o's)

• The use case has too many values for a plain levenshtein and a pre-index of similarity is preferred.

Implementers should note that the default similarity threshold will impact what is passed to levenshtein and the database will need be tuned based on the implementation specific data. For example, if using Canadian Social Insurance Numbers, with a desired levenshtein test of 5 then it would best to set word_similarity_threshold to 0.4 since SIN numbers with 5 edits would be represent a 60% difference in source strings.

SQL Translations / Support

PostgreSQL	Supported (since 2.2)
FirebirdSQL	Not Supported
SQLite	Not Supported

Alias

The alias filter function performs a specialized lookup of values which are known aliases for the input value. For example, William has known aliases of Bill, Will, and Billy . The name alias extension requires the installation and configuration of the IAliasProvider (provided by SanteMPI).

?name.component.value=:(alias)BILL

The implementation of the IAliasPRovider may support any number of aliasing algorithms and alias reference sets. For example, a country-specific implementation may provide known aliases for locations:

?address.component[City].value=:(alias)The Big Apple

Custom Filter Functions

Implementers can write custom filter functions by implementing the following interfaces:

• IQueryExtensionFilter interface which maps and composes the HDSI query expression to/from a .NET Expression tree

• Implementing a .NET Extension Method which allows the HDSI filter to be exposed in a .NET LINQ Expression (and which is used to evaluate the HDSI filter on .NET objects in memory)

• Implementing IDbFilterFunction interface for each persistence interface. This class translates the HDSI Query and LINQ extension into SQL for the appropriate platform.