Queries
Deta Base comes with its own query language for fetching data that match certain conditions. Queries are regular objects/dicts/maps with conventions for different operators for specifying the conditions. You can use queries in Base UI,the Base SDK, or the Base HTTP API.
Base Queries come with a number of operators for querying data, and logical operators AND and OR.
Operators
Queries support the following operators:
Equal
{"age": 22, "name": "Beverly"}
// hierarchical{"user.profile.age": 22, "user.profile.name": "Beverly"}
{"fav_numbers": [2, 4, 8]}
{"time": {"day": "Tuesday", "hour": "08:00"}}
Not Equal
{"user.profile.age?ne": 22}
Less Than
{"user.profile.age?lt": 22}
Greater Than
{"user.profile.age?gt": 22}
Less Than or Equal
{"user.profile.age?lte": 22}
Greater Than or Equal
{"user.profile.age?gte": 22}
Prefix
{"user.id?pfx": "afdk"}
Range
{"user.age?r": [22, 30]}
Contains
{ // if user email contains the substring @deta.sh "user.email?contains": "@deta.sh"}
{ // if berlin is in a list of places lived "user.places_lived_list?contains": "berlin"}
Not Contains
{ // if user email does not contain @deta.sh "user.email?not_contains": "@deta.sh" // 'user.email?!contains' also valid}
{ // if berlin is not in a list of places lived "user.places_lived_list?not_contains": "berlin" // 'user.places_lived_list?!contains' also valid}
?contains
and?not_contains
only works for a list of strings if checking for membership in a list; it does not apply to list of other data types. You can store your lists always as a list of strings if you want to check for membership.
Logical Operators
AND
The entries in a single query object are AND
ed together. For e.g. the query:
{ "active": true, "age?gte": 22}
will retrieve items where active
is true
and age
is greater than or equal to 22
.
The query above would translate to SQL
as:
SELECT * FROM base WHERE active=1 AND age>=22;
OR
Multiple query objects in a list are OR
ed together. For eg. the queries:
[{"age?lte": 30}, {"age?gte": 40}]
will retrieve items where age
is less than equal to 30
or age
is greater than equal to 40
.
The query above would translate to SQL
as:
SELECT * FROM base WHERE age<=30 OR age>=40;
Hierarchy
You can use the period character .
to query for hierarchical fields within the data. For instance if you have the following item in the base:
{ "key": "user-key", "profile": { "age": 22, "active": true }}
Then you can query for the active
and age
within profile
directly:
{ "profile.age": 22, "profile.active": true}
Querying Keys
You need to consider the following when querying on keys:
- The keys must be strings hence the operation values must also be strings.
- The contains and not-contains operators are not supported.
- The
AND
andOR
operations for different query values are not supported.
For e.g. the following queries are invalid:
{ // different AND key queries (invalid query) "key": "a", "key?pfx": "b"}
{ // different OR key queries (invalid query) [{"key?pfx":"a"}, {"key?pfx": "b"}]}