Sorting 

The example-app GraphQL API can sort the records returned by the sortable fields for each model. If you pass the sort argument to a find call, you can pass the fields and directions to sort by.

Sort on a publishedAt Date/Time field for a Post model
await api.post.findMany({
sort: {
publishedAt: "Descending",
},
});
1const [result, refresh] = useFindMany(api.post, {
2 sort: {
3 publishedAt: "Descending",
4 },
5});
6const { data, error, fetching } = result;
1query FindManyPosts($sort: [PostSort!]) {
2 posts(sort: $sort) {
3 edges {
4 node {
5 id
6 publishedAt
7 }
8 }
9 }
10}
Variables
json
{ "sort": { "publishedAt": "Descending" } }
await api.post.findMany({
sort: {
publishedAt: "Descending",
},
});
1const [result, refresh] = useFindMany(api.post, {
2 sort: {
3 publishedAt: "Descending",
4 },
5});
6const { data, error, fetching } = result;

You can sort on findMany, findFirst, and maybeFindFirst read calls using the API Client in Gadget.

Sort order 

Records can be ordered in either Descending or Ascending order for each field they are sorted on. Descending order will return the highest values first, while Ascending order will return the lowest values first. If sorting by multiple fields, each field can have a different order.

Sorting by multiple fields 

Records can be sorted by multiple fields by passing multiple sort orders in an array to your API call, like [ { fieldA: "Descending" }, { fieldB: "Ascending" }]. The first field will be used to order all the records, and then the second field will be used to break ties where records have the same value as the first field. Subsequent sorts will be used to break ties in the second field, etc.

Sort on category and then publishedAt for a Post model
await api.post.findMany({
sort: [{ category: "Ascending" }, { publishedAt: "Descending" }],
});
1const [result, refresh] = useFindMany(api.post, {
2 sort: [
3 { category: "Ascending" },
4 { publishedAt: "Descending" }
5 ]
6});
7const { data, error, fetching } = result;
1query FindManyPosts($sort: [PostSort!]) {
2 posts(filter: $filter) {
3 edges {
4 node {
5 id
6 publishedAt
7 }
8 }
9 }
10}
Variables
json
{ "sort": [{ "category": "Ascending" }, { "publishedAt": "Descending" }] }
await api.post.findMany({
sort: [{ category: "Ascending" }, { publishedAt: "Descending" }],
});
1const [result, refresh] = useFindMany(api.post, {
2 sort: [
3 { category: "Ascending" },
4 { publishedAt: "Descending" }
5 ]
6});
7const { data, error, fetching } = result;

Available fields for sorting 

Records can be sorted by most fields of their model in either the Ascending or Descending direction. The following field types are sortable:

Field typesNotes
string, email, urlSorted using Postgres's alphanumeric sorting rules
rich textSorted using Postgres's alphanumeric sorting rules on the markdown source text
numberSorted by the number's value along the number line
booleanSorted with true higher than false. Descending puts trues first
date / timeSorted by the date and time's value, with earlier dates coming before later dates when using Ascending order
idSorted by the ID's numeric value, with lower IDs coming before higher IDs when using Ascending order
enumSorted by the enum values as strings, using Postgres's alphanumeric sorting rules
jsonSorted by the JSON string representation, using Postgres's alphanumeric sorting rules
vectorSorted by a given vector distance operation

Alphanumeric sorting rules 

Gadget uses Postgres's alphanumeric string sorting rules under the hood, which sorts spaces first, then numbers, then upper case letters, then special symbols, then lower case letters, then nulls.

For example, the following list of strings sorts in this order when sorted Ascending:

1" spaces"
2"10-4"
3"123"
4"Apple"
5"Cube"
6"___"
7"anjou pear"
8"banana"
9null

Sorting by vector distance 

vector fields support vector-specific sort types for returning records in order of distance to a given vector. This implements a nearest neighbor search by querying all your stored vectors to find those that are most similar to an input vector on demand.

To sort by vector distance to a given vector field, pass an object with a to: input field with the vector to sort by.

If we have a document model with an embedding field of type vector, we can sort by the cosine distance to a given vector like so:

Sort on cosine similarity to a given vector
1await api.document.findMany({
2 sort: {
3 embedding: {
4 cosineSimilarityTo: [1, 2, 3], // etc
5 },
6 },
7});
1const [result, refresh] = useFindMany(api.document, {
2 sort: {
3 embedding: {
4 cosineSimilarityTo: [1, 2, 3] // etc
5 }
6 }
7});
8const { data, error, fetching } = result;
1query FindManyDocuments($sort: [DocumentSort!]) {
2 posts(filter: $filter) {
3 edges {
4 node {
5 id
6 embedding
7 }
8 }
9 }
10}
Variables
json
{ "sort": { "embedding": { "cosineSimilarityTo": [1, 2, 3] } } }
1await api.document.findMany({
2 sort: {
3 embedding: {
4 cosineSimilarityTo: [1, 2, 3], // etc
5 },
6 },
7});
1const [result, refresh] = useFindMany(api.document, {
2 sort: {
3 embedding: {
4 cosineSimilarityTo: [1, 2, 3] // etc
5 }
6 }
7});
8const { data, error, fetching } = result;

Available vector distance operations 

Vectors can be sorted by cosine similarity and L2 (Euclidian) distance to a given vector. Gadget generally recommends using cosine similarity for sorting, as it is more robust to changes in vector magnitude.

  • To sort by cosine similarity, pass a sort like { fieldName: { cosineSimilarity: { to: [1,2,3] }}}. Cosine similarity will order by the most similar vectors first, ie cosine similarity descending.
JavaScript
1await api.document.findMany({
2 sort: {
3 embedding: {
4 cosineSimilarityTo: [1, 2, 3],
5 },
6 },
7});
1await api.document.findMany({
2 sort: {
3 embedding: {
4 cosineSimilarityTo: [1, 2, 3],
5 },
6 },
7});
  • To sort by L2 distance, pass a sort like { fieldName: { l2Distance: { to: [1,2,3] }}}. L2 distance will order by the closest vectors first, ie L2 distance ascending.
JavaScript
1await api.document.findMany({
2 sort: {
3 embedding: {
4 l2DistanceTo: [1, 2, 3],
5 },
6 },
7});
1await api.document.findMany({
2 sort: {
3 embedding: {
4 l2DistanceTo: [1, 2, 3],
5 },
6 },
7});

Sorting by least similar 

Vectors can be sorted for least similarity by using one of the vector distance sorts with the order: "Ascending" option. This will return the least similar vectors first.

Sort on cosine similarity to a given vector with least similar first
1await api.document.findMany({
2 sort: {
3 embedding: {
4 cosineSimilarityTo: [1, 2, 3], // etc
5 order: "Ascending",
6 },
7 },
8});
1const [result, refresh] = useFindMany(api.document, {
2 sort: {
3 embedding: {
4 cosineSimilarityTo: [1, 2, 3], // etc
5 order: "Ascending"
6 }
7 }
8});
9const { data, error, fetching } = result;
1query FindManyDocuments($sort: [DocumentSort!]) {
2 posts(filter: $filter) {
3 edges {
4 node {
5 id
6 embedding
7 }
8 }
9 }
10}
Variables
json
{
"sort": { "embedding": { "cosineSimilarityTo": [1, 2, 3], "order": "Ascending" } }
}
1await api.document.findMany({
2 sort: {
3 embedding: {
4 cosineSimilarityTo: [1, 2, 3], // etc
5 order: "Ascending",
6 },
7 },
8});
1const [result, refresh] = useFindMany(api.document, {
2 sort: {
3 embedding: {
4 cosineSimilarityTo: [1, 2, 3], // etc
5 order: "Ascending"
6 }
7 }
8});
9const { data, error, fetching } = result;

Unsortable fields 

Currently, Gadget does not support sorting records by fields of the following types:

  • file
  • encrypted string
  • password
  • has many
  • has one
  • has many through
  • computed

Filtering 

The example-app GraphQL API can filter records when reading to only the records matching certain conditions. If you pass a filter argument to a read API call, only records that match the filter will be returned.

Filter on a boolean isPublished field for a Post model
await api.post.findMany({
filter: {
isPublished: { equals: true },
},
});
1const [result, refresh] = useFindMany(api.post, {
2 filter: {
3 isPublished: {
4 equals: true
5 }
6 },
7});
8const { data, error, fetching } = result;
1query FindManyPosts($filter: [PostFilter!]) {
2 posts(filter: $filter) {
3 edges {
4 node {
5 id
6 isPublished
7 }
8 }
9 }
10}
Variables
json
{ "filter": { "isPublished": { "equals": true } } }
await api.post.findMany({
filter: {
isPublished: { equals: true },
},
});
1const [result, refresh] = useFindMany(api.post, {
2 filter: {
3 isPublished: {
4 equals: true
5 }
6 },
7});
8const { data, error, fetching } = result;

You can filter on findMany, findFirst, and maybeFindFirst read requests using the API Client in Gadget.

Available fields for filtering 

Records can be filtered by most fields of their model, and the type of each model field determines what kind of filters are available in the API. For each field, Gadget uses a specific type for filtering that field type:

Field typesFilter GraphQL Type
string, rich text, email, urlStringFilter
numberFloatFilter and IntFilter
booleanBooleanFilter
date / timeDateTimeFilter
idIDFilter
enumSingleEnumFilter and MultiEnumFilter
jsonJSONFilter
vectorVectorFilter
record stateStateFilter
role listRoleAssignmentFilter
belongs toModelRelationshipFilter
has oneModelRelationshipFilter
has manyModelRelationshipFilter
has many throughModelRelationshipFilter

Each model then has a filter-specific GraphQL type that details the types of filters available on the selected model. For example, if there is a Post model to capture blog post records, there will also be a generated PostFilter GraphQL type. This filter type can be viewed in the API Reference or API Playground.

Want to see your app's filter typing?

Examine the filter typing of your app's API by checking the documentation in the API Playground. All filter types will be visible for all fields on your app's models.

Combining filters 

Records can also be filtered by multiple different fields. If you want to combine filters using boolean logic, nest them under the AND, OR, or NOT keys of a parent filter.

Combine filters - filter for published posts with over 500 words
await api.post.findMany({
filter: {
OR: [{ isPublished: { equals: true } }, { wordCount: { greaterThan: 500 } }],
},
});
1const [result, refresh] = useFindMany(api.post, {
2 filter: {
3 OR: [
4 {
5 isPublished: { equals: true }
6 },
7 {
8 wordCount: { greaterThan: 500 }
9 }
10 ]
11 },
12});
13const { data, error, fetching } = result;
1query FindManyPosts($filter: [PostFilter!]) {
2 posts(filter: $filter) {
3 edges {
4 node {
5 id
6 isPublished
7 }
8 }
9 }
10}
Variables
json
1{
2 "filter": {
3 "OR": [
4 { "isPublished": { "equals": true } },
5 { "wordCount": { "greaterThan": 500 } }
6 ]
7 }
8}
await api.post.findMany({
filter: {
OR: [{ isPublished: { equals: true } }, { wordCount: { greaterThan: 500 } }],
},
});
1const [result, refresh] = useFindMany(api.post, {
2 filter: {
3 OR: [
4 {
5 isPublished: { equals: true }
6 },
7 {
8 wordCount: { greaterThan: 500 }
9 }
10 ]
11 },
12});
13const { data, error, fetching } = result;

Filters can be nested deeply by passing multiple levels of boolean condition filters.

For example, we can fetch orders from a Shopify Order model where the total is between $100 and $200, and the order is either paid or refunded:

Combine filters - filter for orders matching many conditions
1api.shopifyOrder.findMany({
2 filter: {
3 AND: [
4 {
5 AND: [
6 { totalPrice: { greaterThan: 100 } },
7 { totalPrice: { lessThan: 200 } },
8 ],
9 },
10 {
11 OR: [
12 { financialStatus: { equals: "paid" } },
13 { financialStatus: { equals: "refunded" } },
14 ],
15 },
16 ],
17 },
18});
1const [{ data, error, fetching }, refresh] = useFindMany(api.shopifyOrder, {
2 filter: {
3 AND: [
4 { AND: [{ totalPrice: { greaterThan: 100 } }, { totalPrice: { lessThan: 200 } }]},
5 { OR: [{ financialStatus: { equals: "paid"} }, { financialStatus: { equals: "refunded" } }] }
6 ]
7 },
8});
1query FindManyShopifyOrders($filter: [ShopifyOrderFilter!]) {
2 shopifyOrders(filter: $filter) {
3 edges {
4 node {
5 id
6 totalPrice
7 financialStatus
8 }
9 }
10 }
11}
Variables
json
1{
2 "filter": [
3 {
4 "AND": [
5 { "totalPrice": { "greaterThan": 100 } },
6 { "totalPrice": { "lessThan": 200 } }
7 ]
8 },
9 {
10 "OR": [
11 { "financialStatus": { "equals": "paid" } },
12 { "financialStatus": { "equals": "refunded" } }
13 ]
14 }
15 ]
16}
1api.shopifyOrder.findMany({
2 filter: {
3 AND: [
4 {
5 AND: [
6 { totalPrice: { greaterThan: 100 } },
7 { totalPrice: { lessThan: 200 } },
8 ],
9 },
10 {
11 OR: [
12 { financialStatus: { equals: "paid" } },
13 { financialStatus: { equals: "refunded" } },
14 ],
15 },
16 ],
17 },
18});
1const [{ data, error, fetching }, refresh] = useFindMany(api.shopifyOrder, {
2 filter: {
3 AND: [
4 { AND: [{ totalPrice: { greaterThan: 100 } }, { totalPrice: { lessThan: 200 } }]},
5 { OR: [{ financialStatus: { equals: "paid"} }, { financialStatus: { equals: "refunded" } }] }
6 ]
7 },
8});

Filter types 

There are many different filter types available in Gadget. Each filter type corresponds to one or more field types.

Filtering on string and string-like fields 

Fields using the string type or other string-like field types like rich text, email, url can be filtered using string filters. For example, you can filter down to records where a string is equal to a given string:

get students that have the `firstName` field set to "Taylor"
JavaScript
1await api.student.findMany({
2 filter: {
3 firstName: {
4 equals: "Taylor",
5 },
6 },
7});
1await api.student.findMany({
2 filter: {
3 firstName: {
4 equals: "Taylor",
5 },
6 },
7});

Or filter to records where the stored string starts with a given string prefix:

get a page of blog post records that have titles starting with "How to"
JavaScript
1await api.post.findMany({
2 filter: {
3 title: {
4 startsWith: "How to",
5 },
6 },
7});
1await api.post.findMany({
2 filter: {
3 title: {
4 startsWith: "How to",
5 },
6 },
7});

The full list of supported filters on string and string-like fields are are:

equals string filtering 

The equals filter key is used to filter down to records where the string field is equal to a given string. equals does a case-sensitive match to check if the incoming string is exactly equal to the stored string, and will only return records where that is true.

get records with the name exactly equal to "gizmo"
JavaScript
1await api.widget.findMany({
2 filter: {
3 name: {
4 equals: "gizmo",
5 },
6 },
7});
1await api.widget.findMany({
2 filter: {
3 name: {
4 equals: "gizmo",
5 },
6 },
7});
notEquals string filtering 

The notEquals filter key is used to filter down to records where the string field is set to anything other than a given string. notEquals does a case-sensitive match to check if the incoming string is exactly equal to the stored string, and will only return records where that is false.

get widgets that do not have the name "gizmo"
JavaScript
1await api.widget.findMany({
2 filter: {
3 name: {
4 notEquals: "gizmo",
5 },
6 },
7});
1await api.widget.findMany({
2 filter: {
3 name: {
4 notEquals: "gizmo",
5 },
6 },
7});
isSet string filtering 

The isSet filter key is used to filter down to records where the string field is set to anything other than null. isSet will only return records where the value is not null.

get records with a value for the name field
JavaScript
1await api.widget.findMany({
2 filter: {
3 name: {
4 isSet: true,
5 },
6 },
7});
1await api.widget.findMany({
2 filter: {
3 name: {
4 isSet: true,
5 },
6 },
7});

isSet can also be used to check for null by passing isSet: false.

get a page of widget records that do not have a value for the name field
JavaScript
1await api.widget.findMany({
2 filter: {
3 name: {
4 isSet: false,
5 },
6 },
7});
1await api.widget.findMany({
2 filter: {
3 name: {
4 isSet: false,
5 },
6 },
7});
in string filtering 

The in filter key is used to filter down to records where the string field is exactly equal to one of the provided strings. in does a case-sensitive exact comparison between each of the provided strings to the string stored in the database, and will return records where the string field is equal to any of the provided strings. The in filter will never return records where the stored string is null.

get records with the name "gizmo" or "gadget"
JavaScript
1await api.widget.findMany({
2 filter: {
3 name: {
4 in: ["gizmo", "gadget"],
5 },
6 },
7});
1await api.widget.findMany({
2 filter: {
3 name: {
4 in: ["gizmo", "gadget"],
5 },
6 },
7});
notIn string filtering 

The notIn filter key is used to filter down to records where the string field is not equal to any of the provided strings. notIn does a case-sensitive exact comparison between each of the provided strings to the string stored in the database, and will return records where the string field is not equal to any of the provided strings. notIn will also return records where the stored string is null.

get records that do not have the name "gizmo" or "gadget"
JavaScript
1await api.widget.findMany({
2 filter: {
3 name: {
4 notIn: ["gizmo", "gadget"],
5 },
6 },
7});
1await api.widget.findMany({
2 filter: {
3 name: {
4 notIn: ["gizmo", "gadget"],
5 },
6 },
7});
startsWith string filtering 

The startsWith filter key is used to filter down to records where the string field starts with the given string. This filter is case-sensitive and will return records where the string field begins with the specified substring. startsWith will never return records where the string field is set to null.

get records with names starting with "giz", like "gizmo"
JavaScript
1await api.widget.findMany({
2 filter: {
3 name: {
4 startsWith: "giz",
5 },
6 },
7});
1await api.widget.findMany({
2 filter: {
3 name: {
4 startsWith: "giz",
5 },
6 },
7});
lessThan and lessThanOrEqual string filtering 

The lessThan filter key is used to filter down to records where the string field is lexicographically less than a given string. Lexicographically less means that the string comes before the given string in dictionary order, based on the Unicode values of the characters. For example, "apple" is lexicographically less than "banana", and "abc" is lexicographically less than "abd".

get records with names lexicographically less than "gizmo"
JavaScript
1await api.widget.findMany({
2 filter: {
3 name: {
4 lessThan: "gizmo",
5 },
6 },
7});
1await api.widget.findMany({
2 filter: {
3 name: {
4 lessThan: "gizmo",
5 },
6 },
7});

Similarly, the lessThanOrEqual filter key is used to filter down to records where the string field is lexicographically less than or equal to a given string.

get records with names lexicographically less than or equal to "gizmo"
JavaScript
1await api.widget.findMany({
2 filter: {
3 name: {
4 lessThanOrEqual: "gizmo",
5 },
6 },
7});
1await api.widget.findMany({
2 filter: {
3 name: {
4 lessThanOrEqual: "gizmo",
5 },
6 },
7});
greaterThan and greaterThanOrEqual string filtering 

The greaterThan filter key is used to filter down to records where the string field is lexicographically greater than a given string. Lexicographically greater means that the string comes after the given string in dictionary order, based on the Unicode values of the characters. For example, "banana" is lexicographically greater than "apple", and "abd" is lexicographically greater than "abc".

get records with names lexicographically greater than "gizmo"
JavaScript
1await api.widget.findMany({
2 filter: {
3 name: {
4 greaterThan: "gizmo",
5 },
6 },
7});
1await api.widget.findMany({
2 filter: {
3 name: {
4 greaterThan: "gizmo",
5 },
6 },
7});

Similarly, the greaterThanOrEqual filter key is used to filter down to records where the string field is lexicographically greater than or equal to a given string.

get records with names lexicographically >= "gizmo"
JavaScript
1await api.widget.findMany({
2 filter: {
3 name: {
4 greaterThanOrEqual: "gizmo",
5 },
6 },
7});
1await api.widget.findMany({
2 filter: {
3 name: {
4 greaterThanOrEqual: "gizmo",
5 },
6 },
7});
Case-insensitive filtering 

Currently, Gadget has no filters for strings that do a case-insensitive comparison between the stored value and provided value. You can work around this by storing a second copy of the string on your model, and lowercasing it upon storage and upon filtering:

api/models/widget/create.js
JavaScript
1export const run: ActionRun = async ({ params, record }) => {
2 applyParams(record, params);
3 // store the lowercase version of the name as well for filtering later
4 record.lowercaseName = record.name?.toLowerCase();
5 await save(record);
6};
1export const run: ActionRun = async ({ params, record }) => {
2 applyParams(record, params);
3 // store the lowercase version of the name as well for filtering later
4 record.lowercaseName = record.name?.toLowerCase();
5 await save(record);
6};

Then, you can filter on the lowercase name:

get a page of widget records using a case-insensitive filter
JavaScript
1await api.widget.findMany({
2 filter: {
3 lowercaseName: {
4 startsWith: "Giz".toLowerCase(),
5 },
6 },
7});
1await api.widget.findMany({
2 filter: {
3 lowercaseName: {
4 startsWith: "Giz".toLowerCase(),
5 },
6 },
7});

If native case-insensitivity is an important feature for you, please reach out to us in our Discord!

Filtering on number fields 

The number field type supports filtering on numeric fields. Numbers can be filtered by null-ness, equality, and by range.

For example, we can filter a post model by the wordCount number field to get all posts over 500 words:

get blog posts that are over 500 words
JavaScript
1await api.posts.findMany({
2 filter: {
3 wordCount: {
4 greaterThan: 500,
5 },
6 },
7});
1await api.posts.findMany({
2 filter: {
3 wordCount: {
4 greaterThan: 500,
5 },
6 },
7});

Under the hood, your API generates different filter types for <FieldTypeTags.Number/> fields marked as integers or floats. The FloatFilter type is used for fields that have a decimal value, and the IntFilter type is used for fields that have an integer value. The Float scalar type represents signed double-precision fractional values as specified by IEEE 754.

The full list of supported filters on number fields are:

equals number filtering 

The equals filter key is used to filter down to records where the number field is equal to a given number. equals does an exact match to check if the incoming number is exactly equal to the stored number, and will only return records where that is true.

get records with the quantity exactly equal to 10
JavaScript
1await api.widget.findMany({
2 filter: {
3 quantity: {
4 equals: 10,
5 },
6 },
7});
1await api.widget.findMany({
2 filter: {
3 quantity: {
4 equals: 10,
5 },
6 },
7});
notEquals number filtering 

The notEquals filter key is used to filter down to records where the number field is set to anything other than a given number. notEquals does an exact match to check if the incoming number is exactly equal to the stored number, and will only return records where that is false.

get widgets that do not have the quantity equal to 10
JavaScript
1await api.widget.findMany({
2 filter: {
3 quantity: {
4 notEquals: 10,
5 },
6 },
7});
1await api.widget.findMany({
2 filter: {
3 quantity: {
4 notEquals: 10,
5 },
6 },
7});
isSet number filtering 

The isSet filter key is used to filter down to records where the number field is set to anything other than null. isSet will only return records where the value is not null.

get records with a value for the quantity field
JavaScript
1await api.widget.findMany({
2 filter: {
3 quantity: {
4 isSet: true,
5 },
6 },
7});
1await api.widget.findMany({
2 filter: {
3 quantity: {
4 isSet: true,
5 },
6 },
7});

isSet can also be used to check for null by passing isSet: false.

get records that don't have a value for the quantity field
JavaScript
1await api.widget.findMany({
2 filter: {
3 quantity: {
4 isSet: false,
5 },
6 },
7});
1await api.widget.findMany({
2 filter: {
3 quantity: {
4 isSet: false,
5 },
6 },
7});
in number filtering 

The in filter key is used to filter down to records where the number field is exactly equal to one of the provided numbers. in does an exact comparison between each of the provided numbers to the number stored in the database, and will return records where the number field is equal to any of the provided numbers. The in filter will never return records where the stored number is null.

get records with the quantity 10 or 20
JavaScript
1await api.widget.findMany({
2 filter: {
3 quantity: {
4 in: [10, 20],
5 },
6 },
7});
1await api.widget.findMany({
2 filter: {
3 quantity: {
4 in: [10, 20],
5 },
6 },
7});
notIn number filtering 

The notIn filter key is used to filter down to records where the number field is not equal to any of the provided numbers. notIn does an exact comparison between each of the provided numbers to the number stored in the database, and will return records where the number field is not equal to any of the provided numbers. notIn will also return records where the stored number is null.

get records that don't have the quantity 10 or 20
JavaScript
1await api.widget.findMany({
2 filter: {
3 quantity: {
4 notIn: [10, 20],
5 },
6 },
7});
1await api.widget.findMany({
2 filter: {
3 quantity: {
4 notIn: [10, 20],
5 },
6 },
7});
lessThan and lessThanOrEqual number filtering 

The lessThan number key is used to filter down to records where the number field is less than a given number.

get records with quantity less than 10
JavaScript
1await api.widget.findMany({
2 filter: {
3 quantity: {
4 lessThan: 10,
5 },
6 },
7});
1await api.widget.findMany({
2 filter: {
3 quantity: {
4 lessThan: 10,
5 },
6 },
7});

Similarly, the lessThanOrEqual filter key is used to filter down to records where the number field is less than or equal to a given number.

get records with quantity less than or equal to 10
JavaScript
1await api.widget.findMany({
2 filter: {
3 quantity: {
4 lessThanOrEqual: 10,
5 },
6 },
7});
1await api.widget.findMany({
2 filter: {
3 quantity: {
4 lessThanOrEqual: 10,
5 },
6 },
7});
greaterThan and greaterThanOrEqual number filtering 

The greaterThan filter key is used to filter down to records where the number field is greater than a given number.

get records with quantity greater than 10
JavaScript
1await api.widget.findMany({
2 filter: {
3 quantity: {
4 greaterThan: 10,
5 },
6 },
7});
1await api.widget.findMany({
2 filter: {
3 quantity: {
4 greaterThan: 10,
5 },
6 },
7});

Similarly, the greaterThanOrEqual filter key is used to filter down to records where the number field is greater than or equal to a given number.

get records with quantity greater than or equal to 10
JavaScript
1await api.widget.findMany({
2 filter: {
3 quantity: {
4 greaterThanOrEqual: 10,
5 },
6 },
7});
1await api.widget.findMany({
2 filter: {
3 quantity: {
4 greaterThanOrEqual: 10,
5 },
6 },
7});

Filtering on boolean fields 

boolean fields can be filtered to return only records with the boolean set to true, false, or where the value is set to anything at all.

For example, we can check if the isPublished boolean field is set to true with equals: true:

get blog posts that are published
JavaScript
1await api.posts.findMany({
2 filter: {
3 isPublished: {
4 equals: true,
5 },
6 },
7});
1await api.posts.findMany({
2 filter: {
3 isPublished: {
4 equals: true,
5 },
6 },
7});

Or find those that are not published with equals: false:

get blog posts that are not published
JavaScript
1await api.posts.findMany({
2 filter: {
3 isPublished: {
4 equals: false,
5 },
6 },
7});
1await api.posts.findMany({
2 filter: {
3 isPublished: {
4 equals: false,
5 },
6 },
7});

The list of supported filter keys for boolean fields is:

  • equals
  • notEquals
  • isSet
equals boolean filtering 

The equals filter key is used to filter down to records where the boolean field is equal to true or false. equals will only return records where the stored boolean value matches the provided one.

get records with the isPublished field set to true
JavaScript
1await api.widget.findMany({
2 filter: {
3 isPublished: {
4 equals: true,
5 },
6 },
7});
1await api.widget.findMany({
2 filter: {
3 isPublished: {
4 equals: true,
5 },
6 },
7});
notEquals boolean filtering 

The notEquals filter key is used to filter down to records where the boolean field is set to anything other than a given boolean. notEquals checks if the incoming boolean is exactly equal to the stored boolean, and will only return records where that is false. This can include records where the stored value is set to null.

get widgets that don't have the isPublished field set to true
JavaScript
1await api.widget.findMany({
2 filter: {
3 isPublished: {
4 notEquals: true,
5 },
6 },
7});
1await api.widget.findMany({
2 filter: {
3 isPublished: {
4 notEquals: true,
5 },
6 },
7});
isSet boolean filtering 

The isSet filter key is used to filter down to records where the boolean field is set to anything other than null. isSet will only return records where the value is not null.

get widgets with a value for the isPublished field
JavaScript
1await api.widget.findMany({
2 filter: {
3 isPublished: {
4 isSet: true,
5 },
6 },
7});
1await api.widget.findMany({
2 filter: {
3 isPublished: {
4 isSet: true,
5 },
6 },
7});

isSet can also be used to check for null by passing isSet: false.

get widgets that don't have a value for the isPublished field
JavaScript
1await api.widget.findMany({
2 filter: {
3 isPublished: {
4 isSet: false,
5 },
6 },
7});
1await api.widget.findMany({
2 filter: {
3 isPublished: {
4 isSet: false,
5 },
6 },
7});

Filtering on date / time fields 

The date / time field type supports filtering on date-time fields. Date-time fields can be filtered by null-ness, equality, and by date range.

For example, we can filter a post model by the createdAt date / time field to get all posts written in the past week:

get blog posts written last week
JavaScript
1const oneWeekAgo = new Date(Date.now() - 604800000);
2await api.post.findMany({
3 filter: {
4 createdAt: {
5 after: oneWeekAgo,
6 },
7 },
8});
1const oneWeekAgo = new Date(Date.now() - 604800000);
2await api.post.findMany({
3 filter: {
4 createdAt: {
5 after: oneWeekAgo,
6 },
7 },
8});

Date times can be provided to the filter API as Date objects or as ISO 8601 date-time strings.

use Date objects or date-time strings
JavaScript
1// all datetime filters support being passed real `Date` objects
2await api.post.findMany({
3 filter: {
4 createdAt: {
5 after: new Date("2024-01-01T00:00:00Z"),
6 },
7 },
8});
9
10// and also support being passed ISO 8601 date-time strings
11await api.post.findMany({
12 filter: {
13 createdAt: {
14 after: "2024-01-01T00:00:00Z",
15 },
16 },
17});
1// all datetime filters support being passed real `Date` objects
2await api.post.findMany({
3 filter: {
4 createdAt: {
5 after: new Date("2024-01-01T00:00:00Z"),
6 },
7 },
8});
9
10// and also support being passed ISO 8601 date-time strings
11await api.post.findMany({
12 filter: {
13 createdAt: {
14 after: "2024-01-01T00:00:00Z",
15 },
16 },
17});

DateTime objects are only stored to the nearest millisecond. If you are filtering with smaller increments of time, such as nanoseconds, the value will be truncated to only include milliseconds.

The full list of supported filters on date / time fields are:

equals date-time filtering 

The equals filter key is used to filter down to records where the date / time field is equal to a given date-time. equals does an exact match to check if the incoming date-time is exactly equal to the stored date-time, and will only return records where that is true.

get posts created exactly at a specific date-time
JavaScript
1await api.post.findMany({
2 filter: {
3 createdAt: {
4 equals: "2023-07-15T10:00:00Z",
5 },
6 },
7});
1await api.post.findMany({
2 filter: {
3 createdAt: {
4 equals: "2023-07-15T10:00:00Z",
5 },
6 },
7});
notEquals date-time filtering 

The notEquals filter key is used to filter down to records where the date / time field is set to anything other than a given date-time. notEquals does an exact match to check if the incoming date-time is exactly equal to the stored date-time, and will only return records where that is false.

get posts not created at a specific date-time
JavaScript
1await api.post.findMany({
2 filter: {
3 createdAt: {
4 notEquals: "2023-07-15T10:00:00Z",
5 },
6 },
7});
1await api.post.findMany({
2 filter: {
3 createdAt: {
4 notEquals: "2023-07-15T10:00:00Z",
5 },
6 },
7});
isSet date-time filtering 

The isSet filter key is used to filter down to records where the date / time field is set to anything other than null. isSet will only return records where the value is not null.

get a page of posts that have a value for the createdAt field
JavaScript
1await api.post.findMany({
2 filter: {
3 createdAt: {
4 isSet: true,
5 },
6 },
7});
1await api.post.findMany({
2 filter: {
3 createdAt: {
4 isSet: true,
5 },
6 },
7});

isSet can also be used to check for null by passing isSet: false.

get posts that don't have a value for the createdAt field
JavaScript
1await api.post.findMany({
2 filter: {
3 createdAt: {
4 isSet: false,
5 },
6 },
7});
1await api.post.findMany({
2 filter: {
3 createdAt: {
4 isSet: false,
5 },
6 },
7});
in date-time filtering 

The in filter key is used to filter down to records where the date / time field is exactly equal to one of the provided date-times. in does an exact comparison between each of the provided date-times to the date-time stored in the database, and will return records where the date-time field is equal to any of the provided date-times. The in filter will never return records where the stored date-time is null.

get posts created at either of the specified date-times
JavaScript
1await api.post.findMany({
2 filter: {
3 createdAt: {
4 in: ["2023-07-15T10:00:00Z", "2023-07-16T12:00:00Z"],
5 },
6 },
7});
1await api.post.findMany({
2 filter: {
3 createdAt: {
4 in: ["2023-07-15T10:00:00Z", "2023-07-16T12:00:00Z"],
5 },
6 },
7});
notIn date-time filtering 

The notIn filter key is used to filter down to records where the date / time field is not equal to any of the provided date-times. notIn does an exact comparison between each of the provided date-times to the date-time stored in the database, and will return records where the date-time field is not equal to any of the provided date-times. notIn will also return records where the stored date-time is null.

get posts not created at either of the specified date-times
JavaScript
1await api.post.findMany({
2 filter: {
3 createdAt: {
4 notIn: ["2023-07-15T10:00:00Z", "2023-07-16T12:00:00Z"],
5 },
6 },
7});
1await api.post.findMany({
2 filter: {
3 createdAt: {
4 notIn: ["2023-07-15T10:00:00Z", "2023-07-16T12:00:00Z"],
5 },
6 },
7});
lessThan and lessThanOrEqual date-time filtering 

The lessThan date / time key is used to filter down to records where the date / time field is less than a given date-time.

get posts created before a specific date-time
JavaScript
1await api.post.findMany({
2 filter: {
3 createdAt: {
4 lessThan: "2023-07-15T10:00:00Z",
5 },
6 },
7});
1await api.post.findMany({
2 filter: {
3 createdAt: {
4 lessThan: "2023-07-15T10:00:00Z",
5 },
6 },
7});

Similarly, the lessThanOrEqual filter key is used to filter down to records where the date / time field is less than or equal to a given date-time.

get posts created before or exactly at a specific date-time
JavaScript
1await api.post.findMany({
2 filter: {
3 createdAt: {
4 lessThanOrEqual: "2023-07-15T10:00:00Z",
5 },
6 },
7});
1await api.post.findMany({
2 filter: {
3 createdAt: {
4 lessThanOrEqual: "2023-07-15T10:00:00Z",
5 },
6 },
7});
greaterThan and greaterThanOrEqual date-time filtering 

The greaterThan filter key is used to filter down to records where the date / time field is greater than a given date-time.

get posts created after a specific date-time
JavaScript
1await api.post.findMany({
2 filter: {
3 createdAt: {
4 greaterThan: "2023-07-15T10:00:00Z",
5 },
6 },
7});
1await api.post.findMany({
2 filter: {
3 createdAt: {
4 greaterThan: "2023-07-15T10:00:00Z",
5 },
6 },
7});

Similarly, the greaterThanOrEqual filter key is used to filter down to records where the date / time field is greater than or equal to a given date-time.

get posts created at or after a specific date-time
JavaScript
1await api.post.findMany({
2 filter: {
3 createdAt: {
4 greaterThanOrEqual: "2023-07-15T10:00:00Z",
5 },
6 },
7});
1await api.post.findMany({
2 filter: {
3 createdAt: {
4 greaterThanOrEqual: "2023-07-15T10:00:00Z",
5 },
6 },
7});
before date-time filtering 

The before filter key is an alias for the lessThan filter, used to filter down to records where the date / time field is before a given date-time.

get posts created before a specific date-time
JavaScript
1await api.post.findMany({
2 filter: {
3 createdAt: {
4 before: "2023-07-15T10:00:00Z",
5 },
6 },
7});
1await api.post.findMany({
2 filter: {
3 createdAt: {
4 before: "2023-07-15T10:00:00Z",
5 },
6 },
7});
after date-time filtering 

The after filter key is an alias for the greaterThan filter, used to filter down to records where the date / time field is after a given date-time.

get posts created after a specific date-time
JavaScript
1await api.post.findMany({
2 filter: {
3 createdAt: {
4 after: "2023-07-15T10:00:00Z",
5 },
6 },
7});
1await api.post.findMany({
2 filter: {
3 createdAt: {
4 after: "2023-07-15T10:00:00Z",
5 },
6 },
7});

Filtering on idfields 

The id field type supports filtering on ID fields. IDs can be filtered by null-ness, equality, and by range.

For example, we can filter a student model by the id field to get all students with specific IDs:

get students with ids that match 1, 2, or 3
JavaScript
1await api.student.findMany({
2 filter: {
3 id: {
4 in: ["1", "2", "3"],
5 },
6 },
7});
1await api.student.findMany({
2 filter: {
3 id: {
4 in: ["1", "2", "3"],
5 },
6 },
7});

IDs can be passed as strings or numbers and Gadget will ensure the types are converted before comparison.

The full list of supported filters on id fields are:

equals ID filtering 

The equals filter key is used to filter down to records where the id field is equal to a given ID. equals does an exact match to check if the incoming ID is exactly equal to the stored ID, and will only return records where that is true.

get students that have the id exactly equal to 1
JavaScript
1await api.student.findMany({
2 filter: {
3 id: {
4 equals: "1",
5 },
6 },
7});
1await api.student.findMany({
2 filter: {
3 id: {
4 equals: "1",
5 },
6 },
7});
notEquals ID filtering 

The notEquals filter key is used to filter down to records where the id field is set to anything other than a given ID. notEquals does an exact match to check if the incoming ID is exactly equal to the stored ID, and will only return records where that is false.

get students that do not have the id equal to 1
JavaScript
1await api.student.findMany({
2 filter: {
3 id: {
4 notEquals: "1",
5 },
6 },
7});
1await api.student.findMany({
2 filter: {
3 id: {
4 notEquals: "1",
5 },
6 },
7});
isSet ID filtering 

The isSet filter key is used to filter down to records where the id field is set to anything other than null. isSet will only return records where the value is not null.

get students that have a value for the id field
JavaScript
1await api.student.findMany({
2 filter: {
3 id: {
4 isSet: true,
5 },
6 },
7});
1await api.student.findMany({
2 filter: {
3 id: {
4 isSet: true,
5 },
6 },
7});

isSet can also be used to check for null by passing isSet: false.

get students that do not have a value for the id field
JavaScript
1await api.student.findMany({
2 filter: {
3 id: {
4 isSet: false,
5 },
6 },
7});
1await api.student.findMany({
2 filter: {
3 id: {
4 isSet: false,
5 },
6 },
7});
in ID filtering 

The in filter key is used to filter down to records where the id field is exactly equal to one of the provided IDs. in does an exact comparison between each of the provided IDs to the ID stored in the database, and will return records where the ID field is equal to any of the provided IDs. The in filter will never return records where the stored ID is null.

get students that have the id 1, 2, or 3
JavaScript
1await api.student.findMany({
2 filter: {
3 id: {
4 in: ["1", "2", "3"],
5 },
6 },
7});
1await api.student.findMany({
2 filter: {
3 id: {
4 in: ["1", "2", "3"],
5 },
6 },
7});
notIn ID filtering 

The notIn filter key is used to filter down to records where the id field is not equal to any of the provided IDs. notIn does an exact comparison between each of the provided IDs to the ID stored in the database, and will return records where the ID field is not equal to any of the provided IDs. notIn will also return records where the stored ID is null.

get students that do not have the id 1, 2, or 3
JavaScript
1await api.student.findMany({
2 filter: {
3 id: {
4 notIn: ["1", "2", "3"],
5 },
6 },
7});
1await api.student.findMany({
2 filter: {
3 id: {
4 notIn: ["1", "2", "3"],
5 },
6 },
7});
lessThan and lessThanOrEqual ID filtering 

The lessThan id key is used to filter down to records where the id field is less than a given ID.

get students that have id less than 10
JavaScript
1await api.student.findMany({
2 filter: {
3 id: {
4 lessThan: "10",
5 },
6 },
7});
1await api.student.findMany({
2 filter: {
3 id: {
4 lessThan: "10",
5 },
6 },
7});

Similarly, the lessThanOrEqual filter key is used to filter down to records where the id field is less than or equal to a given ID.

get students that have id less than or equal to 10
JavaScript
1await api.student.findMany({
2 filter: {
3 id: {
4 lessThanOrEqual: "10",
5 },
6 },
7});
1await api.student.findMany({
2 filter: {
3 id: {
4 lessThanOrEqual: "10",
5 },
6 },
7});
greaterThan and greaterThanOrEqual ID filtering 

The greaterThan filter key is used to filter down to records where the id field is greater than a given ID.

get students that have id greater than 10
JavaScript
1await api.student.findMany({
2 filter: {
3 id: {
4 greaterThan: "10",
5 },
6 },
7});
1await api.student.findMany({
2 filter: {
3 id: {
4 greaterThan: "10",
5 },
6 },
7});

Similarly, the greaterThanOrEqual filter key is used to filter down to records where the id field is greater than or equal to a given ID.

get students that have id greater than or equal to 10
JavaScript
1await api.student.findMany({
2 filter: {
3 id: {
4 greaterThanOrEqual: "10",
5 },
6 },
7});
1await api.student.findMany({
2 filter: {
3 id: {
4 greaterThanOrEqual: "10",
5 },
6 },
7});

Filtering on enum Fields 

The enum field type supports filtering on enum fields, which can store predefined string values. Enum fields can be filtered by null-ness, equality, inequality, and containment of values when multiple selections are allowed.

The following filters are supported on enum fields:

isSet enum filtering 

The isSet filter key is used to filter down to records where the enum field is set to anything other than null. isSet will only return records where the value is not null.

get tickets that have a status set
JavaScript
1await api.tickets.findMany({
2 filter: {
3 status: {
4 isSet: true,
5 },
6 },
7});
1await api.tickets.findMany({
2 filter: {
3 status: {
4 isSet: true,
5 },
6 },
7});

isSet can also be used to check for null by passing isSet: false.

get tickets that do not have a status set
JavaScript
1await api.tickets.findMany({
2 filter: {
3 status: {
4 isSet: false,
5 },
6 },
7});
1await api.tickets.findMany({
2 filter: {
3 status: {
4 isSet: false,
5 },
6 },
7});
equals enum filtering 

The equals filter key is used to filter down to records where the enum field is equal to a given value. equals does an exact match to check if the incoming value is exactly equal to the stored value and will only return records where that is true.

get tickets that have a status of "in-progress"
JavaScript
1await api.tickets.findMany({
2 filter: {
3 status: {
4 equals: "in-progress",
5 },
6 },
7});
1await api.tickets.findMany({
2 filter: {
3 status: {
4 equals: "in-progress",
5 },
6 },
7});

For enum fields where the allowMultiple option is true, the stored value is an array of strings. equals must be passed an array of strings for these fields, and will only return records where the stored value is an array containing exactly the provided values.

equals on enum fields where the allowMultiple option is true is case sensitive and order sensitive. It will only return records where the stored value is an array containing exactly the provided values in the provided order. If you need to check partial matches, use the contains filter.

get clients that have communication set to exactly ["SMS", "Email"]
JavaScript
1await api.client.findMany({
2 filter: {
3 communication: {
4 equals: ["SMS", "Email"],
5 },
6 },
7});
1await api.client.findMany({
2 filter: {
3 communication: {
4 equals: ["SMS", "Email"],
5 },
6 },
7});

This filter would match a record like { communication: ["SMS", "Email"] }. This filter would not match records like:

  • { communication: ["SMS"] } because there's an element missing
  • { communication: ["Email", "SMS"] } because the order is wrong
  • { communication: ["sms", "email"] } because the casing doesn't match
  • { communication: ["SMS", "Email", "In-Person"] } because there is an extra element
notEquals enum filtering 

The notEquals filter key is used to filter down to records where the enum field is set to anything other than a given value. notEquals does an exact match to check if the incoming value is exactly equal to the stored value and will only return records where that is false.

get tickets that do not have a status of "backlog"
JavaScript
1await api.tickets.findMany({
2 filter: {
3 status: {
4 notEquals: "backlog",
5 },
6 },
7});
1await api.tickets.findMany({
2 filter: {
3 status: {
4 notEquals: "backlog",
5 },
6 },
7});

For enum fields where the allowMultiple option is true, the stored value is an array of strings. notEquals must be passed an array of strings for these fields, and will only return records where the stored value does not exactly equal the provided values.

get clients that have communication set to anything else
JavaScript
1await api.client.findMany({
2 filter: {
3 communication: {
4 notEquals: ["SMS", "Email"],
5 },
6 },
7});
1await api.client.findMany({
2 filter: {
3 communication: {
4 notEquals: ["SMS", "Email"],
5 },
6 },
7});

This filter would not match a record like { communication: ["SMS", "Email"] }. This filter will match records like:

  • { communication: null } because null isn't equal to the provided value
  • { communication: ["SMS"] } because there's an element missing
  • { communication: ["Email", "SMS"] } because the order is wrong
  • { communication: ["sms", "email"] } because the casing doesn't match
  • { communication: ["SMS", "Email", "In-Person"] } because there is an extra element
in enum filtering 

The in filter key is used to filter down to records where the enum field is exactly equal to one of the provided values. in does an exact comparison between each of the provided values to the value stored in the database and will return records where the enum field is equal to any of the provided values. The in filter will never return records where the stored value is null.

get tickets that have a status of "backlog" or "in-progress"
JavaScript
1await api.tickets.findMany({
2 filter: {
3 status: {
4 in: ["backlog", "in-progress"],
5 },
6 },
7});
1await api.tickets.findMany({
2 filter: {
3 status: {
4 in: ["backlog", "in-progress"],
5 },
6 },
7});
contains enum filtering 

When the allowMultiple field configuration is turned on for a enum field, the contains filter is available for checking if the stored record value contains one or more provided values. contains checks if all provided filter values are present but does not perform an exact match, so there can be more stored values.

get clients that allow both "Sms" and "Email" communication
JavaScript
1await api.client.findMany({
2 filter: {
3 communication: {
4 contains: ["SMS", "Email"],
5 },
6 },
7});
1await api.client.findMany({
2 filter: {
3 communication: {
4 contains: ["SMS", "Email"],
5 },
6 },
7});

This filter would match records like:

  • { communication: ["SMS", "Email"] }
  • { communication: ["Email", "SMS"] }
  • { communication: ["SMS", "Email", "In-Person"] }

This filter would not match records like:

  • { communication: ["SMS"] } because there's an element missing
  • { communication: ["sms", "email"] } because the casing doesn't match

This will return all client records that have both the "Sms" and "Email" options selected. Because this is not an exact match, this will also include client records that have additional options selected beyond "Sms" and "Email".

Filtering on json Fields 

The json field type supports filtering on JSON fields. JSON fields can be filtered by their structure, values, and presence.

For example, we can filter a system setup model by the configuration json field to get all setups where the JSON contains a specific key-value pair:

get system setups where the configuration contains { foo: "bar" }
JavaScript
1await api.systemSetup.findMany({
2 filter: {
3 configuration: {
4 matches: { foo: "bar" },
5 },
6 },
7});
1await api.systemSetup.findMany({
2 filter: {
3 configuration: {
4 matches: { foo: "bar" },
5 },
6 },
7});

We can also filter on JSON arrays stored in json fields, for example, filtering by the tags field on a product model:

get products where tags JSON array contains "Accessory"
JavaScript
1await api.product.findMany({
2 filter: {
3 tags: {
4 matches: "Accessory",
5 },
6 },
7});
1await api.product.findMany({
2 filter: {
3 tags: {
4 matches: "Accessory",
5 },
6 },
7});

The full list of supported filters on json fields are:

When filtering on json fields, you will almost always want to use a matches filter.

isSet JSON filtering 

The isSet filter key is used to filter down to records where the json field is set to anything other than null. isSet will only return records where the value is not null.

get records that have a value for the configuration field
JavaScript
1await api.systemSetup.findMany({
2 filter: {
3 configuration: {
4 isSet: true,
5 },
6 },
7});
1await api.systemSetup.findMany({
2 filter: {
3 configuration: {
4 isSet: true,
5 },
6 },
7});

isSet can also be used to check for null by passing isSet: false.

get records that do not have a value for the configuration field
JavaScript
1await api.systemSetup.findMany({
2 filter: {
3 configuration: {
4 isSet: false,
5 },
6 },
7});
1await api.systemSetup.findMany({
2 filter: {
3 configuration: {
4 isSet: false,
5 },
6 },
7});
equals JSON filtering 

The equals filter key is used to filter down to records where the json field is exactly equal to a given JSON value. equals does an exact match to check if the incoming JSON is exactly equal to the stored JSON, and will only return records where that is true.

get records with configuration { foo: "bar" }
JavaScript
1await api.systemSetup.findMany({
2 filter: {
3 configuration: {
4 equals: { foo: "bar" },
5 },
6 },
7});
1await api.systemSetup.findMany({
2 filter: {
3 configuration: {
4 equals: { foo: "bar" },
5 },
6 },
7});
notEquals JSON filtering 

The notEquals filter key is used to filter down to records where the json field is set to anything other than a given JSON value. notEquals does an exact match to check if the incoming JSON is exactly equal to the stored JSON, and will only return records where that is false.

get records with other configurations
JavaScript
1await api.systemSetup.findMany({
2 filter: {
3 configuration: {
4 notEquals: { foo: "bar" },
5 },
6 },
7});
1await api.systemSetup.findMany({
2 filter: {
3 configuration: {
4 notEquals: { foo: "bar" },
5 },
6 },
7});
in JSON filtering 

The in filter key is used to filter down to records where the json field is exactly equal to one of the provided JSON values. in does an exact comparison between each of the provided JSON values to the JSON stored in the database, and will return records where the JSON field is equal to any of the provided values. The in filter will never return records where the stored JSON is null.

get records with configuration equal to either value
JavaScript
1await api.systemSetup.findMany({
2 filter: {
3 configuration: {
4 in: [{ foo: "bar" }, { fizz: "buzz" }],
5 },
6 },
7});
1await api.systemSetup.findMany({
2 filter: {
3 configuration: {
4 in: [{ foo: "bar" }, { fizz: "buzz" }],
5 },
6 },
7});

This filter would match records like:

  • { configuration: { foo: "bar" } }
  • { configuration: { fizz: "buzz" } }

This filter would not match records like:

  • { configuration: { foo: "something else" } } because the value of foo is not bar
  • { configuration: { foo: "bar", fizz: "buzz" } } because the overall value is not equal to either provided value
  • { configuration: { foo: { bar: "baz" } } } because the value of foo is not a string
  • { configuration: null } because the whole value is missing
notIn JSON filtering 

The notIn filter key is used to filter down to records where the json field is not equal to any of the provided JSON values. notIn does an exact comparison between each of the provided JSON values to the JSON stored in the database, and will return records where the JSON field is not equal to any of the provided values. notIn will also return records where the stored JSON is null.

get records that don't have the configuration equal to either value
JavaScript
1await api.systemSetup.findMany({
2 filter: {
3 configuration: {
4 notIn: [{ foo: "bar" }, { fizz: "buzz" }],
5 },
6 },
7});
1await api.systemSetup.findMany({
2 filter: {
3 configuration: {
4 notIn: [{ foo: "bar" }, { fizz: "buzz" }],
5 },
6 },
7});
matches JSON filtering 

The matches filter key filters down to records where the json field contains the provided JSON structure as a subset. The matches filter uses the @> operator under the hood in Postgres.

get records with configuration containing { foo: "bar" }
JavaScript
1await api.systemSetup.findMany({
2 filter: {
3 configuration: {
4 matches: { foo: "bar" },
5 },
6 },
7});
1await api.systemSetup.findMany({
2 filter: {
3 configuration: {
4 matches: { foo: "bar" },
5 },
6 },
7});

This filter would match records like:

  • { configuration: { foo: "bar" } }
  • { configuration: { foo: "bar", fizz: "buzz" } }

This filter would not match records like:

  • { configuration: { foo: "buzz" } } because the value of foo is not bar
  • { configuration: { foo: { bar: "buzz" } } } because the value of foo is not a string
  • { configuration: { one: "two" } } because the key foo is missing
  • { configuration: null } because the whole value is missing

The matches filter can be used to match nested structures as well:

get records that have the configuration containing { foo: { bar: "baz" } }
JavaScript
1await api.systemSetup.findMany({
2 filter: {
3 configuration: {
4 matches: { foo: { bar: "baz" } },
5 },
6 },
7});
1await api.systemSetup.findMany({
2 filter: {
3 configuration: {
4 matches: { foo: { bar: "baz" } },
5 },
6 },
7});

This will return records where the configuration field contains a nested structure matching { foo: { bar: "baz" } }, allowing extra keys at either level.

The matches filter can also be used to match nested arrays within the JSON structure. This allows you to filter records where a JSON field contains an array with specific elements.

For example, to get a page of system setup records that have the configuration containing a foo array with the element "bar":

get records with configuration partial matching provided value
JavaScript
1await api.systemSetup.findMany({
2 filter: {
3 configuration: {
4 matches: { foo: ["bar"] },
5 },
6 },
7});
1await api.systemSetup.findMany({
2 filter: {
3 configuration: {
4 matches: { foo: ["bar"] },
5 },
6 },
7});

This filter will match records where the configuration field contains an array with the element "bar" in the foo array. The array can contain additional elements, but it must include "bar" as one of its elements.

For example, this filter would match records like:

  • { configuration: { foo: ["bar"] } }
  • { configuration: { foo: ["bar", "baz"] } }
  • { configuration: { foo: ["baz", "bar"] } }

This filter would not match records like:

  • { configuration: { foo: ["baz"] } } because the array does not contain "bar"
  • { configuration: { foo: [] } } because the array is empty
  • { configuration: { foo: null } } because the array is missing
  • { configuration: { foo: "bar" } } because the value is not an array

Filtering on vector fields 

vector fields store an array of floats, called vectors, in the database. Vector filters can be used to only return records that have a vector field similar to an input vector.

Most often, vector filters are paired with vector sorts to find vectors that are most similar to an input vector. Read more about vector sorts here. Vector filters allow you to cap the maximum vector distance in a vector similarity search.

Filtering on vector similarity 

Vector similarity features allow you to filter records down to only those that have a stored vector that is similar to an input vector. Vector similarity allows you to implement semantic search.

To filter based on similarity, you must provide an input vector to compare all the stored vectors against, and a similarity threshold to filter using. When you query, each stored vector is compared to the provided vector using the operator you've selected, and only those passing the threshold are returned. Vector similarity searches support two measures of similarity: cosine similarity and L2 distance.

For example, we can filter the document model to return only records with a vector that is similar to the input vector [1, 2, 3] in the embedding field:

get a page of records with a vector that is similar to the input vector [1, 2, 3]
JavaScript
1const records = await api.document.findMany({
2 filter: {
3 embedding: {
4 cosineSimilarity: {
5 to: [1, 2, 3],
6 greaterThan: 0.8,
7 },
8 },
9 },
10});
1const records = await api.document.findMany({
2 filter: {
3 embedding: {
4 cosineSimilarity: {
5 to: [1, 2, 3],
6 greaterThan: 0.8,
7 },
8 },
9 },
10});

This will return records from the document model that have a vector stored in the embedding field that is similar to the input vector [1, 2, 3] as measured by cosine similarity.

When filtering vectors, the input vector in the to field must have the same dimensions (length) as the stored vector. Trying to filter by a vector with different dimensions will throw an error and not return any data.

cosineSimilarity vector filtering 

cosineSimilarity filters compute the cosine similarity between each stored vector and the input vector and then return records where the cosine similarity passes the provided threshold. The threshold can be a greaterThan threshold, which will return only records that are more similar than the threshold in the result, or a lessThan threshold, which will return vectors that are less similar than the threshold.

For more on the math behind cosine similarity and other vector distance measures, see this great post.

For example, to return only vectors that have a cosine similarity of at least 0.8 to a given input vector, you can use the cosineSimilarity filter with an input vector:

get a page of records with a vector that is similar to the input vector [1, 2, 3]
JavaScript
1const records = await api.document.findMany({
2 filter: {
3 embedding: {
4 cosineSimilarity: {
5 to: [1, 2, 3],
6 greaterThan: 0.8,
7 },
8 },
9 },
10});
1const records = await api.document.findMany({
2 filter: {
3 embedding: {
4 cosineSimilarity: {
5 to: [1, 2, 3],
6 greaterThan: 0.8,
7 },
8 },
9 },
10});

Usually, this similarity filter would be paired with a similarity sort to return the most similar vectors first:

get the most similar embeddings to a given input vector, with a minimum cosine distance of 0.8
JavaScript
1const records = await api.document.findMany({
2 sort: {
3 embedding: {
4 cosineSimilarityTo: [1,