Use table filters
Use the table filters with JSON DB
use the table filters with JSON DB
The Get data actions query data and return records. Most of these actions provide one or more ways to filter the results. One such action is to use a table filter.
A table filter has a similar purpose to the SQL WHERE clause. It is an expression that uses syntax common to all C family programming languages, including C, C++, C#, Java, JavaScript, Go, Rust, and so forth.
The database engine determines when a record should be included in the results by running the table filter expression against the record to see if it returns true. A table filter expression includes one or more fields from the table along with comparison operators, values, functions, and Boolean expressions. The expression language is documented here.
How does a table filter work?
A table filter is applied immediately after the database retrieves a record and before it is included in the results. When you filter records using an index with a partial key or a key range, the database uses the index to find the first matching result. It then uses the optional table filter expression to determine if the record should be included in the results. If so, it adds the record to the results. If not, it does not add the record to the results and moves to the next record and checks again. This process repeats until the query processes all desired records
What are the rules for creating table filter expressions?
The default is an empty string, which does not filter records.
A non-empty string is parsed by the database and used to filter results.
Inside the filter expression, JSON requires an embedded double quote to be escaped using the backslash character.
If an expression is invalid, the results contain an error with a description of the problem.
How well do table filters perform?
Table filters are fast and run inside the database engine process. The database compiles each table filter expression into optimized bytecode. When the database retrieves a record, it passes a pointer to the record buffer into a function that runs the bytecode. The function returns true when the record passes the filter test.
A table filter is fast, but it is applied to every record being evaluated by the query. The more you can reduce the number of records being evaluated by a table filter, the faster the results. For example, you can use the "getRecordsInKeyRange" or "getRecordsByPartialKeyRange" actions to limit the range of records being filtered.
In some cases, the most efficient way to query all the records in a table is to use a table filter. This is true when a field does not have an index that can be used to filter results. It also applies when a query needs an index to look up more than about 33% of the records in a table. This is because reading records requires about 66% less IO than using an index to look up records.
Examples
The "tableFilter" examples work in any "getRecords…" action that supports the "tableFilter" property.
{
"api": "db",
"action": "getRecordsByIndex",
"params": {
"tableName": "athlete",
"indexName": "id_pk",
"tableFilter": "strnicmp( name, \"mi\", 2 ) == 0"
},
"responseOptions": {
"includeFields": ["name"]
},
"authToken": "replaceWithValidAuthTokenFromServer",
"debug": "none"
}
Note
The functions strcmp(), stricmp(), strncmp() and strnicmp() do string comparisons. They return 0 when the strings match, -1 when the source string is sorted before the comparison string, and 1 when the source string is sorted after the target string. stricmp() and strnicmp() do case insensitive comparisons. strncmp() and strnicmp() limit the comparison to the first N bytes. If a UTF-8 character uses more than one byte, such as "é", increase N accordingly. For example, "tableFilter": "strncmp( name, \"Pelé\", 5 ) == 0". Lastly, strncmp() is the same as strncmp().
These examples show how to filter records by comparing a field value to a constant string.
Exact string match
Include records where the value of a "name" field exactly matches the string "Pele".
"tableFilter": "name == \"Pele\"" "tableFilter": "strcmp( name, \"Pele\" ) == 0" "tableFilter": "strncmp( name, \"Pele\", 4 ) == 0" "tableFilter": "match( name, \"Pele\" ) == 1"
Exact string non-match
Include records where the value of a "name" field does not match the string "Pele". Matches include "Michael Jordan", and "Babe Ruth".
"tableFilter": "name != \"Pele\"" "tableFilter": "strcmp( name, \"Pele\" ) != 0" "tableFilter": "strncmp( name, \"Pele\", 4 ) != 0" "tableFilter": "match( name, \"Pele\" ) == 0"
Less-than string match
Include records where the value of a "name" field is less than the string "Pele". Matches include "Michael Jordan", and "Babe Ruth".
"tableFilter": "name < \"Pele\"" "tableFilter": "strcmp( name, \"Pele\" ) < 0" "tableFilter": "strncmp( name, \"Pele\", 4 ) < 0"
Less-than-equal string match
Include records where the value of a "name" field is less than the string "Pele". Matches include "Pele", "Michael Jordan", and "Babe Ruth".
"tableFilter": "name <= \"Pele\"" "tableFilter": "strcmp( name, \"Pele\" ) <= 0" "tableFilter": "strncmp( name, \"Pele\", 4 ) <= 0"
Greater-than string match
Include records where the value of a "name" field is greater than the string "Pele". Matches include "Wayne Gretzky".
"tableFilter": "name > \"Pele\"" "tableFilter": "strcmp( name, \"Pele\" ) > 0" "tableFilter": "strncmp( name, \"Pele\", 4 ) > 0"
Greater-than-equal string match
Include records where the value of a "name" field is greater than the string "Pele". Matches include "Pele" and "Wayne Gretzky".
"tableFilter": "name >= \"Pele\"" "tableFilter": "strcmp( name, \"Pele\" ) >= 0" "tableFilter": "strncmp( name, \"Pele\", 4 ) >= 0"
These examples show how to filter records by comparing a field value to a constant string while doing a case insensitive comparison. For example, the following strings match each other when doing a case insensitive comparison: "Pele", "pele", "PELE", "PeLe", "pELE", "pElE", and so forth.
Case insensitive string match
Include records where the value of a "name" field matches with the string "pele" while doing a case insensitive comparison. Matches include "Pele" and "PELE" .
"tableFilter": "stricmp( name, \"pele\" ) == 0" "tableFilter": "strnicmp( name, \"pele\", 4 ) == 0" "tableFilter": "matchi( name, \"pele\" ) == 1"
Case insensitive string non-match
Include records where the value of a "name" field does not match the string "pele" while doing a case insensitive comparison. Matches include "Michael Jordan", and "Babe Ruth".
"tableFilter": "stricmp( name, \"pele\" ) != 0" "tableFilter": "strnicmp( name, \"pele\", 4 ) != 0" "tableFilter": "matchi( name, \"pele\" ) == 0"
Case insensitive less-than string match
Include records where the value of a "name" field is less than the string "pele" while doing a case insensitive comparison. Matches include "Michael Jordan", and "Babe Ruth".
"tableFilter": "stricmp( name, \"pele\" ) < 0" "tableFilter": "strnicmp( name, \"PELE\", 4 ) < 0"
Case insensitive less-than-equal string match
Include records where the value of a "name" field is less than or equal to the string "pele" while doing a case insensitive comparison. Matches include "Pele", "Michael Jordan", and "Babe Ruth".
"tableFilter": "stricmp( name, \"PeLe\" ) <= 0" "tableFilter": "strnicmp( name, \"pele\", 4 ) <= 0"
Case insensitive greater-than string match
Include records where the value of a "name" field is greater than the string "pele" while doing a case insensitive comparison. Matches include "Wayne Gretzky".
"tableFilter": "stricmp( name, \"pElE\" ) > 0" "tableFilter": "strnicmp( name, \"pele\", 4 ) > 0"
Case insensitive greater-than-equal string match
Include records where the value of a "name" field is greater than or equal to the string "pele" while doing a case insensitive comparison. Matches include "Pele" and "Wayne Gretzky".
"tableFilter": "stricmp( name, \"PELE\" ) >= 0" "tableFilter": "strnicmp( name, \"pele\", 4 ) >= 0"
These examples show how to filter records by comparing against the first N characters of a field.
Note
When using
strncmp(), characters in the comparison string are not included in the comparison when they occur after the specified character count. For example,"tableFilter": "strncmp( name, \"Mike\", 1 ) == 0", matches names where the first letter is "M".When using
strncmp(), you can use a string of any length for partial comparison. Be sure to specify the proper number of characters to include in the comparison. For example,"tableFilter": "strncmp( name, \"Mich\", 4 ) == 0", matches names where the first four letters are "Mich", such as "Michael Jordan" and "Michael Schumacher", but not "Muhammad Ali".When using st
rncmp() to compare a UTF-8 string that uses more than one byte per character increase N accordingly. For example,"tableFilter": "strncmp( name, \"Pelé\", 5 ) == 0"matches all names in the name field that begin with "Pelé".
Partial string match
Include records where the first character in a "name" field exactly matches the string "M". Matches include "Michael Jordan", "Michael Schumacher", and "Muhammad Ali".
"tableFilter": "strncmp( name, \"M\", 1 ) == 0" "tableFilter": "match( name, \"M*\" ) == 1"
Partial string non-match
Include records where the first character in a "name" field does not match the string "M". Matches include "Babe Ruth", "Pele", and "Wayne Gretzky", but not "Michael Jordan", "Michael Schumacher", and "Muhammad Ali".
"tableFilter": "strncmp( name, \"M\", 1 ) != 0" "tableFilter": "match( name, \"M*\" ) == 0"
Partial less-than string match
Include records where the first character in a "name" field is less than the string "M". Matches include "Babe Ruth".
"tableFilter": "strncmp( name, \"M\", 1 ) < 0"
Partial less-than-equal string match
Include records where the first character in a "name" field is less than or equal to the string "M". Matches include "Babe Ruth", "Michael Jordan", "Michael Schumacher", and "Muhammad Ali".
"tableFilter": "strncmp( name, \"M\", 1 ) <= 0"
Partial greater-than string match
Include records where the first character in a "name" field is greater than the string "M". Matches include "Pele" and "Wayne Gretzky".
"tableFilter": "strncmp( name, \"M\", 1 ) > 0"
Partial greater-than-equal string match
Include records where the first character in a "name" field is greater than or equal to the string "M". Matches include "Michael Jordan", "Michael Schumacher", "Muhammad Ali", "Pele" and "Wayne Gretzky".
"tableFilter": "strncmp( name, \"M\", 1 ) >= 0"
These examples show how to filter records by comparing against the first N characters of a field while doing case insensitive string comparisons.
Case insensitive partial string match
Include records where the first character in a "name" field matches the string "M" or "m" while doing a case insensitive comparison. Matches include "Michael Jordan", "Michael Schumacher", and "Muhammad Ali".
"tableFilter": "strnicmp( name, \"m\", 1 ) == 0" "tableFilter": "matchi( name, \"m*\" ) == 1"
Case insensitive partial string non-match
Include records where the first character in a "name" field does not match the string "M" or "m" while doing a case insensitive comparison. Matches include "Babe Ruth", "Pele", and "Wayne Gretzky", but not "Michael Jordan", "Michael Schumacher", and "Muhammad Ali".
"tableFilter": "strnicmp( name, \"m\", 1 ) != 0" "tableFilter": "matchi( name, \"m*\" ) == 0"
Case insensitive partial less-than string match
Include records where the first character in a "name" field is less than the string "M" or "m" while doing a case insensitive comparison. Matches include "Babe Ruth".
"tableFilter": "strnicmp( name, \"m\", 1 ) < 0"
Case insensitive partial less-than-equal string match
Include records where the first character in a "name" field is less than or equal to the string "M" or "m" while doing a case insensitive comparison. Matches include "Babe Ruth", "Michael Jordan", "Michael Schumacher", and "Muhammad Ali".
"tableFilter": "strnicmp( name, \"m\", 1 ) <= 0"
Case insensitive partial greater-than string match
Include records where the first character in a "name" field is greater than the string "M" or "m" while doing a case insensitive comparison. Matches include "Pele" and "Wayne Gretzky".
"tableFilter": "strnicmp( name, \"m\", 1 ) > 0"
Case insensitive partial greater-than-equal string match
Include records where the first character in a "name" field is greater than or equal to the string "M" or "m" while doing a case insensitive comparison. Matches include "Michael Jordan", "Michael Schumacher", "Muhammad Ali", "Pele" and "Wayne Gretzky".
"tableFilter": "strnicmp( name, \"m\", 1 ) >= 0"
The locate() function finds the location of one string within another. It returns 0 when it does not find an exact, case sensitive match; otherwise, it returns the character position of the first match. The first character is position 1.
The substring() function returns a string out of another string starting at a specific character position for the specified number of bytes up to the end of the string. The first character is position 1.
The right() function returns a string out of another string starting at the end for the specified number of bytes up to the beginning.
The left() function returns a string out of another string starting at the beginning for the specified number of bytes up to the end.
Tip
A match can be made case insensitive by wrapping the field name in the lower() function and comparing it to a lower case string constant: "tableFilter": "locate( lower(favoriteSaying), \"there\" ) == 1"
Contains string
Include records where the "favoriteSaying" field contains "there". Matches include "There is no 'i' in team but there is in win." and "Once something is a passion, the motivation is there.".
"tableFilter": "locate( favoriteSaying, \"there\" ) >= 1"
Does not contain string
Include records where the "favoriteSaying" field does not contain "is". Matches include "Every strike brings me closer to the next home run." and "Float like a butterfly, sting like a bee.".
"tableFilter": "locate( favoriteSaying, \"is\" ) == 0"
Contains string after the Nth character
Include records where "favoriteSaying" field contains "there" after the first 10 bytes. Matches include "There is no 'i' in team but there is in win.".
"tableFilter": "locate( favoriteSaying, \"there\", 10 ) >= 10"
Contains beginning-string
Include records where the first 3 characters in a "name" field match the string "Muh". Matches include "Muhammad Ali".
"tableFilter": "left(name, 3) == \"Muh\""
Does not contain beginning-string
Include records where the first 3 characters in a "name" field do not match the string "Muh". Matches include strings like "Babe Ruth", "Pele", and "Wayne Gretzky", but not "Muhammad Ali".
"tableFilter": "left(name, 3) != \"Muh\""
Contains end-string
Include records where the 3 rightmost characters in a "name" field match the string "Ali". Matches include strings like "Babe Ruth", "Pele", and "Wayne Gretzky", but not "Muhammad Ali".
"tableFilter": "right(name,3) == \"Ali\""
Does not contain end-string
Include records where the 3 rightmost characters in a "name" field do not match the string "Ali". Matches include strings like "Babe Ruth", "Pele", and "Wayne Gretzky", but not "Muhammad Ali".
"tableFilter": "right(name,3) != \"Ali\""
Contains mid-string
Include records where the 3rd and 4th characters in a "name" field match the string "ch". Matches include strings like "Michael Jordan" and "Michael Schumacher".
"tableFilter": "substring(name,3,2) == \"ch\""
Does not contains mid-string
Include records where the 3rd and 4th characters in a "name" field do not match the string "ch". Matches include strings like "Michael Jordan" and "Michael Schumacher".
"tableFilter": "substring(name,3,2) != \"ch\""
The match() function does exact string comparisons using wildcard characters. It returns 1 when the comparison matches and 0 when it doesn't.
The period "." character matches any one character.
The asterisk "*" character matches any number of characters; thus, when the asterisk character is used, it should be the last wildcard character.
Wildcard string match
Include records where the characters in a "name" field match the wildcard string "M.c.a*". Matches include "Michael Jordan" and "Michael Schumacher".
"tableFilter": "match( name, \"M.c.a*\" ) == 1"
Wildcard string non-match
Include records where the characters in a "name" field do not match the wildcard string "M.c.a*". Matches include strings like "Babe Ruth", "Pele", and "Wayne Gretzky", but not "Michael Jordan", "Michael Schumacher", and "Muhammad Ali".
"tableFilter": "match( name, \"M.c.a*\" ) == 0"
Wildcard end-string match
Include records where the 3 rightmost characters in a "name" field match the wildcard string "A.i". Matches include strings like "Muhammad Ali".
"tableFilter": "match( right(name,3), \"A.i\" ) == 1"
Wildcard end-string non-match
Include records where the 3 rightmost characters in a "name" field do not match the wildcard string "A.i". Matches include strings like "Babe Ruth", "Pele", and "Wayne Gretzky", but not "Muhammad Ali".
"tableFilter": "match( right(name,3), \"A.i\" ) == 0"
Wildcard mid-string match
Include records where the 3rd and 4th characters in a "name" field match the wildcard string "c.". Matches include strings like "Michael Jordan" and "Michael Schumacher".
"tableFilter": "match( substring(name,3,2), \"c.\" ) == 1"
Wildcard mid-string non-match
Include records where the 3rd and 4th characters in a "name" field do not match the wildcard string "c.". Matches include strings like "Babe Ruth", "Pele", "Muhammad Ali", and "Wayne Gretzky", but not "Michael Jordan" and "Michael Schumacher".
"tableFilter": "match( substring(name,3,2), \"c.\" ) == 0"
The matchi() function does case insensitive string comparisons using wildcard characters. It returns 1 when the comparison matches and 0 when it does not.
The period "." character matches any one character.
The asterisk "*" character matches any number of characters; thus, when the asterisk character is used, it should be the last wildcard character.
Case insensitive wildcard string match
Include records where the characters in a "name" field match the wildcard string "M.c.a*" while doing a case insensitive comparison. Matches include "Michael Jordan" and "Michael Schumacher".
"tableFilter": "matchi( name, \"m.c.a*\" ) == 1"
Case insensitive wildcard string non-match
Include records where the characters in a "name" field do not match the wildcard string "M.c.a*" while doing a case insensitive comparison. Matches include strings like "Babe Ruth", "Pele", and "Wayne Gretzky", but not "Michael Jordan", "Michael Schumacher", and "Muhammad Ali".
"tableFilter": "matchi( name, \"M.C.A*\" ) == 0"
Case insensitive wildcard end-string match
Include records where the 3 rightmost characters in a "name" field match the wildcard string "A.I" while doing a case insensitive comparison. Matches include strings like "Muhammad Ali".
"tableFilter": "matchi( right(name,3), \"A.I\" ) == 1"
Case insensitive wildcard end-string non-match
Include records where the 3 rightmost characters in a "name" field do not match the wildcard string "a.i" while doing a case insensitive comparison. Matches include strings like "Babe Ruth", "Pele", and "Wayne Gretzky", but not "Muhammad Ali".
"tableFilter": "matchi( right(name,3), \"a.i\" ) == 0"
Case insensitive wildcard mid-string match
Include records where the 3rd and 4th characters in a "name" field match the wildcard string "C." while doing a case insensitive comparison. Matches include strings like "Michael Jordan" and "Michael Schumacher".
"tableFilter": "matchi( substring(name,3,2), \"C.\" ) == 1"
Case insensitive wildcard mid-string non-match
Include records where the 3rd and 4th characters in a "name" field do not match the wildcard string ".H" while doing a case insensitive comparison. Matches include strings like "Babe Ruth", "Pele", "Muhammad Ali", and "Wayne Gretzky", but not "Michael Jordan" and "Michael Schumacher".
"tableFilter": "matchi( substring(name,3,2), \".H\" ) == 0"
Compare string fields in the same record
You can compare the values of two fields in the same record.
"tableFilter": "name == favoritesaying" "tableFilter": "name != favoritesaying" "tableFilter": "name >= favoritesaying" "tableFilter": "strncmp( name, favoritesaying, 1 ) > 0" "tableFilter": "stricmp( name, favoritesaying ) == 0" "tableFilter": "strnicmp( name, favoritesaying, 1 ) > 0"