Querying Lists / FQM - operators, datatypes, examples

	operator	description	datatype	query input	example

operator

description

datatype

query input

example

1

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

user input text

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

Matches all records where field1 contains est
field1 with a value of testing matches

2

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

user input text

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

Matches all records where field1 contains est
field1 with a value of testing matches

3

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

user input text

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

Matches all records where field1 starts with the letters test
field1 with a value of testing matches

4

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

user input text

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

Matches all records where field1 starts with the letters test
field1 with a value of testing matches

5

equals

$eq

string
uuid
date
number
Boolean

string

user input text
dropdown of pre-populated values
True / False (Boolean)

6

not equal to

$ne

string
date
number
Boolean

string

user input text
dropdown of pre-populated values
True / False (Boolean)

7

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

8

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)

dropdown of pre-populated values
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

9

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)

dropdown of pre-populated values
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

10

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

11

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

12

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

13

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