Querying Lists / FQM - operators, datatypes, examples

contains (regex)

$regex

when character(s) anywhere within a field (with a single value) match the string provided by the user

note: this operator utilizes regex, and will be replaced in the UI in Trillium

string

string

• user input text

{"field1": {"$regex": "est"}}

• Matches all records where field1 contains est

• field1 with a value of testing matches

contains

$contains

when character(s) anywhere within a field (with a single value) match the string provided by the user

note: API support for this operator was added in Sunflower, but will not be available in the UI until Trillium

string

string

• user input text

{"field1": {"$contains": "est"}}

• Matches all records where field1 contains est

• field1 with a value of testing matches

starts with (regex)

$regex

when the first character(s) in a field (with a single value) match the string provided by the user

note: this operator utilizes regex, and will be replaced in the UI in Trillium

string

string

• user input text

{"field1": {"$regex": "^test"}}

• Matches all records where field1 starts with the letters test

• field1 with a value of testing matches

starts with

$starts_with

when the first character(s) in a field (with a single value) match the string provided by the user

note: API support for this operator was added in Sunflower, but will not be available in the UI until Trillium

string

string

• user input text

{"field1": {"$starts_with": "^test"}}

• Matches all records where field1 starts with the letters test

• field1 with a value of testing matches

equals

$eq

• string

• uuid

• date

• number

• Boolean

string

• user input text

• dropdown of pre-populated values

• True / False (Boolean)

not equal to

$ne

• string

• date

• number

• Boolean

string

• user input text

• dropdown of pre-populated values

• True / False (Boolean)

is null/empty

$empty

matches when a specific field is null, or has an empty value

• string

• date

• number

• Boolean

• uuid

string

• True / False dropdown

{"field1": {"$empty": "true"}}

• Matches all records where field1 is null or empty

in

$in

when a field (with a single value) matches any of the values provided by a user (the user can input multiple values)

• string

• uuid

array* (multiple values)

1. dropdown of pre-populated values

2. user input text

*as of the Sunflower release, there are differences between what types of user input is supported in the UI vs API

• for string datatypes, text with pre-defined values from a dropdown is the only supported option in the UI

• for uuid datatypes, users can input comma separated uuids into the UI

{"field1": {"$in": ["value1", "value2"]}

• Matches all records where field1 is value1 or value2

not in

$nin

when a field (with a single value) matches none of the values provided by a user (the user can input multiple values)

• string

• uuid

array* (multiple values)

1. dropdown of pre-populated values

2. user input text

*as of the Sunflower release, there are differences between what types of user input is supported in the UI vs API

• for string datatypes, text with pre-defined values from a dropdown is the only supported option in the UI

• for uuid datatypes, users can input comma separated uuids into the UI

{"field1": {"$nin": ["value1", "value2"]}}

• Matches all records where field1 has a value, and is not value1 or value2

contains any

$contains_any

returns records where a field (that can contain multiple values) matches any of values provided by a user (the user can input multiple values)

array of:

• strings

• uuids

array* (multiple values)

• dropdown of pre-populated values

• user input text (API only)

*as of the Sunflower release, there are differences between what types of user input is supported in the UI vs API

{"arrayField": {"$contains_any": ["value1", "value2]}}

• Matches all records where arrayField contains value1 or value2

• records only containing value or value12 would not match

not contain any*

$not_contains_any

return records where a field (that can contain multiple values) does not contain any of values provided by a user (the user can input multiple values)

array of:

• strings

• uuids

array* (multiple values)

• dropdown of pre-populated values

• user input text (API only)

*as of the Sunflower release, there are differences between what types of user input is supported in the UI vs API

{"arrayField": {"not_contains_any": ["value1", "value2]}}

• Matches all records where arrayField does not contain value1 or value2

• records with value1 and value4 would not be returned

• records containing value or value12 would be returned

contains all

$contains_all

returns records where a field (that can contain multiple values) matches all of values provided by a user (the user can input multiple values)

array of:

• strings

• uuids

array* (multiple values)

• dropdown of pre-populated values

• user input text (API only)

*as of the Sunflower release, there are differences between what types of user input is supported in the UI vs API

{"arrayField": {"$contains_all": ["value1", "value2]}}

• Matches all records where arrayField contains value1 AND value2

• records with value1, value2 and value4 would be returned

• records only containing value or value12 would not be returned

• ordering of the values does not matter

not contains all

$not_contains_all

returns records where a field (that can contain multiple values) does not contain all of values provided by a user (the user can input multiple values)

array of:

• strings

• uuids

array* (multiple values)

• dropdown of pre-populated values

• user input text

{"arrayField": {"$not_contains_all": ["value1", "value2]}}

• Matches all records where arrayField does not contain value1 AND value2

• records with value1 and value4 would be returned

• records only containing value or value12 would be returned

• ordering of the values does not matter