Querying data

Overview

A query is a read operation on models that returns a set of data or results. You can query LoopBack models using a Node API and a REST API, using filters, as outlined in the following table. Filters specify criteria for the returned data set. The capabilities and options of the two APIs are the same–the only difference is the syntax used in HTTP requests versus Node function calls. In both cases, LoopBack models return JSON.

LoopBack supports the following kinds of filters:

• Fields filter
• Include filter
• Limit filter
• Order filter
• Skip filter
• Where filter

See Filters below for more information.

Examples

See additional examples of each kind of filter in the individual articles on filters (for example Where filter).

An example of using the find() method with both a where and a limit filter:

Account.find({where: {name: 'John'}, limit: 3}, function(err, accounts) { /* ... */ });

Equivalent using REST:

/accounts?filter[where][name]=John&filter[limit]=3

Filters

In both REST and Node API, you can use any number of filters to define a query.

LoopBack supports a specific filter syntax: it’s a lot like SQL, but designed specifically to serialize safely without injection and to be native to JavaScript. Previously, only the PersistedModel.find() method (and related methods) supported this syntax.

The following table describes LoopBack’s filter types:

REST syntax

Specify filters in the HTTP query string:

?filter_filterType_=_spec_&_filterType_=_spec_....

The number of filters that you can apply to a single request is limited only by the maximum URL length, which generally depends on the client used.

Node syntax

Specify filters as the first argument to find() and findOne(): 

{ filterType: spec, filterType: spec, ... }

There is no theoretical limit on the number of filters you can apply.

Where:

• filterType is the filter: where, include, order, limit, skip, or fields.
• spec is the specification of the filter: for example for a where filter, this is a logical condition that the results must match. For an include filter it specifies the related fields to include.

Using “stringified” JSON in REST queries

Instead of the standard REST syntax described above, you can also use “stringified JSON” in REST queries. To do this, simply use the JSON specified for the Node syntax, as follows:

?filter={ Stringified-JSON }

where Stringified-JSON is the stringified JSON from Node syntax. However, in the JSON all text keys/strings must be enclosed in quotes (“).

For example: GET /api/activities/findOne?filter={"where":{"id":1234}}

Filtering arrays of objects

The loopback-filters module implements LoopBack’s filter syntax. Using this module, you can filter arrays of objects using the same filter syntax supported by MyModel.find(filter).

Here is a basic example using the new module.

var data = [{n: 1}, {n: 2}, {n: 3, id: 123}];
var filter = {where: {n: {gt: 1}}, skip: 1, fields: ['n']};
var filtered = require('loopback-filters')(data, filter);
console.log(filtered); // => [{n: 3}]

For a bit more detail, say you are parsing a comma-separated value (CSV) file, and you need to output all values where the price column is between 10 and 100. To use the LoopBack filter syntax you would need to either create your own CSV connector or use the memory connector, both of which require some extra work not related to your actual goal.

Once you’ve parsed the CSV (with a module like node-csv) you will have an array of objects like this, for example (but with, say, 10,000 unique items):

[
  {price: 85, id: 79},
  {price: 10, id: 380},
  //...
]

To filter the rows you could use generic JavaScript like this:

data.filter(function(item) {
  return item.price < 100 && item.price >= 10
});

This is pretty simple for filtering, but sorting, field selection, and more advanced operations become a bit tricky. On top of that, you are usually accepting the parameters as input.

For example:

var userInput = {min: 10, max: 100}

data.filter(function(item) {
  return item.price < userInput.min && item.price >= userInput.max
});

You can rewrite this easily as a LoopBack filter:

filter(data, {where: {input: {gt: userInput.min, lt: userInput.max}}})

Or if you just adopt the filter object syntax as user input:

filter(data, userInput)

But loopback-filters supports more than just excluding and including. It supports field selection (including / excluding fields), sorting, geo/distance sorting, limiting and skipping. All in a declarative syntax that is easily created from user input.

As a LoopBack user this is a pretty powerful thing. Typically, you will have learned how to write some complex queries using the find() filter syntax; before you would need to figure out how to do the same thing in JavaScript (perhaps using a library such as underscore). Now with the loopback-filters module, in your client application you can re-use the same exact filter object you were sending to the server to filter the database without having to interact with a LoopBack server at all.

Filtering nested properties

Loopback supports filtering nested properties in three NoSQL connectors: Mongodb, Cloudant, Memory.

For example, model User contains a nested property user.address.tags.tag:

db.define('User', {
  name: {type: String, index: true},
  email: {type: String, index: true},
  address: {
    street: String,
    city: String,
    tags: [
      {
        tag: String,
      }
    ]
  }
});

users can do a nested query like User.find({where: {'address.tags.tag': 'business'}}.

Data source connectors for relational databases don’t support filtering nested properties.

Sanitizing filter and data objects

Filters are very powerful and flexible. To prevent them from creating potential security risks, LoopBack sanitize filter objects as follows:

1. Normalize undefined values

The policy is controlled by the normalizeUndefinedInQuery setting at datasource or model level. There are three options:

• ‘nullify’: Set undefined to null
• ‘throw’: Throw an error if undefined is found
• ‘ignore’: Remove undefined. This is the default behavior if normalizeUndefinedInQuery is not configured

For example:

{
  "db": {
    "name": "db",
    "connector": "memory",
    "normalizeUndefinedInQuery": "ignore"
  }
}

{
  "project": {
    "dataSource": "db",
    "public": true,
    "normalizeUndefinedInQuery": "throw"
  }
}

1. Prohibit hidden/protected properties from being searched

Hidden or protected properties can expose sensitive information if they are allowed to be searched.

LoopBack introduces prohibitHiddenPropertiesInQuery setting at datasource/model level to control if hidden/protected properties can be used in the where object. By default, its value is true.

For example,

server/datasources.json:

{
  "db": {
    "name": "db",
    "connector": "memory",
    "prohibitHiddenPropertiesInQuery": true
  }
}

With the following model definition:

{
  "name": "MyModel",
  "hidden": ["secret"],
  "properties": {
    "name": "string",
    "secret": "string"
  }
}

MyModel.find({where: {secret: 'guess'}}); will be sanitized as MyModel.find({where: {}}; and a warning will be printed on the console:

Potential security alert: hidden/protected properties ["secret"] are used in query.

1. Report circular references

If the filter object has circular references, LoopBack throws an error as follows:

{
  message: 'The query object is circular',
  statusCode: 400,
  code: 'QUERY_OBJECT_IS_CIRCULAR'
}

1. Constrain the maximum depth of query and data objects

Deep filter objects may be mapped to very complex queries that can potentially break your application. To mitigate such risks, LoopBack allows you to configure maxDepthOfQuery and maxDepthOfData in datasource/model settings. The default value is 12. Please note the depth is calculated based on the level of child properties of an JSON object.

For example:

{
  "db": {
    "name": "db",
    "connector": "memory",
    "maxDepthOfQuery": 5,
    "maxDepthOfData": 16
  }
}

If the filter or data object exceeds the maximum depth, an error will be reported:

{
  message: 'The query object exceeds maximum depth 5',
  statusCode: 400,
  code: 'QUERY_OBJECT_TOO_DEEP'
}

Per method invocation constraints

The constraints can also be passed in as options argument for method calls. Method level settings take precedence over model/datasource configuration. For example,

MyModel.find(filter, {
  prohibitHiddenProperties: false,
  maxDepthOfQuery: 8,
}, callback);

For remote methods invoked via REST APIs, the following values are set by default:

• prohibitHiddenPropertiesInQuery: true
• maxDepthOfQuery: 12
• maxDepthOfData: 32

To override such defaults, you can override createOptionsFromRemotingContext(ctx) method of the model class. See documentation for more details.