To search ScriptDB for records that match some criteria, use the method db.query(). To make a query, you specify a subset of the object you want to find. For example, to find all objects with an employee_id of 1:

var db = ScriptDb.getMyDb();
var result = db.query({employee_id: 1});

Iterating over results

ScriptDbResult objects resemble iterators in Java in that they have hasNext() and next() methods to iterate over the results. A common idiom to iterate over the results would be:

while (result.hasNext()) {
  var current = result.next();
  // Do something with "current".
}

There's also result.getSize(), which returns the number of results for the query. If you specified a limit or are using pagination, the number of results will be that limit or page size, at most.

Logical operators

Now suppose there are other items in the database that match employee_id: 1. You can specify more items in the query object to refine the results. The object we saved on the previous page also contains a key called type. You can separate multiple criteria with commas:

var result = db.query({employee_id: 1, type: 'employee'});

This query will return any items that have both the employee_id of 1 and type of 'employee'. You can think of adding comma-separated criteria to the query as a logical AND.

Now suppose that you need to find all the people whose addresses are in either Mississippi (MS) or New York (NY). In this case, you need some way of doing a logical OR. This is accomplished with the method db.anyOf(values):

var result = db.query({
  address: {
    state: db.anyOf(['NY', 'MS'])
  }
});

Note that the keys in your query need not exist at the top level of the stored map; a query will search nested keys as well. Likewise, you can query on city and state:

var result = db.query({
  address: db.anyOf([
    {city: 'Jackson',  state: 'MS'},
    {city: 'New York', state: 'NY'}
    ]
});

The last remaining bit is logical NOT, provided by the method db.not(value):

var result = db.query({
  address: {
    city: 'Cleveland',
    state: db.not('TX')
  }
});

Comparison operators

ScriptDB also provides methods to check for relative values instead of equality. For example, db.greaterThan(value), returns objects with a value greater than specified:

var result = db.query({
  is_person: true,
  age: db.greaterThan(21)
});

The methods db.greaterThanOrEqualTo(value), db.lessThan(value), and db.lessThanOrEqualTo(value) work in a similar fashion.

The method db.between(value1, value2), takes two arguments: an inclusive lower bound and an exclusive upper bound (in math terms, a half-open interval). Thus, to find all teenagers in your database, you'd do a query like this:

var result = db.query({
  is_person: true,
  age: db.between(13, 20)
});

Pagination

There are two different ways to handle pagination of results: either startAt(number) plus limit(number) or paginate(pageNumber, pageSize):

// Returns 50 results starting at result #100.
var result = db.query({}).startAt(100).limit(50);
// Returns the 4th page of 25 result pages.
var result = db.query({}).paginate(3, 25);

If a limit is not specified, the query defaults to 200 results.

Sorting

If you have a lot of results, it's helpful to order them. The method sortBy(fieldPath, direction, strategy) can be called on a query result with the enums SortDirection and SortStrategy as arguments. By default, the sort is in ASCENDING, LEXICAL order. If the field is numeric, you can tell ScriptDB to use the NUMERIC strategy, so that 10 sorts after 2. Likewise, you can specify the DESCENDING direction:

db.query({address: db.anyValue()}).sortBy('address.city');
db.query({address: db.anyValue()}).sortBy('address.zip', db.NUMERIC);
db.query({address: db.anyValue()}).sortBy('address.zip', db.DESCENDING);
db.query({address: db.anyValue()}).sortBy('address.zip', db.ASCENDING, db.LEXICAL);

Strings that look like numbers will sort properly when sorted with NUMERIC; queries like {foo: 23} and {foo: '23'} are thus equivalent. Likewise, booleans sort as 1 for true and 0 for false and can be queried against numeric 1/0 and the strings 'true'/'false'.

Type equivalences

For convenience, automatic type equivalences are available. This means that if you have an object with a numeric value, you can query it as a string. For example, if you store the object {a: 23}, you can query on {a: '23'}. The inverse is also true. This also works, albeit somewhat differently, for booleans. All non-zero numerics are equivalent to boolean true; zero is equivalent to false. Non-empty strings are equivalent to boolean true; empty strings are equivalent to false. All object and array values are true. It's also useful to note that the strings '0' and 'false' are equivalent to boolean true; only an empty string is equivalent to false. So, if you want to find all objects where the key x is set to anything (i.e. non-null in traditional database systems), you can query {field: db.anyOf([true, false])}; db.anyValue() does the same thing.

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 3.0 License, and code samples are licensed under the Apache 2.0 License. For details, see our Site Policies.