Advanced Queries for Collections

To filter or group items stored in a collection, you can use queries. The examples shown here are written in JavaScript, but the pattern remains the same for other languages.

Filtering By Regular Expressions

pattern = {"field":{"$regex" : "YOUR_REGEXP" , "$options" : "i" }};

The i option means "case-insensitive".

Example:

function list_ok(result) {
alert("ok=" + JSON.stringify(result));
}

function list_ko(result) {
alert("ko=" + JSON.stringify(result));
}

var my_pattern = {};
my_pattern.first_name = {"$regex" : ".*julia.*" , "$options" : "i" };

col_sample_collection.listCallback(my_pattern,{},list_ok,list_ko);

In this example, col_sample_collection is the ID of the collection which will be filtered by first_name. The query retrieves all items from the collection where first_name equals “Julia” or “julia”.

Filtering on Arrays

Suppose, your collection contains arrays and, for example, looks like this:

[{
    "details": {
        "country": [
            "FR",
            "UK",
            "USA"
        ],
        "site": "ABC"
    }
},
{
    "details": {
        "country": [
            "FR",
            "ES",
            "IT"
        ],
        "site": "ABC"
    }
},
{
    "details": {
        "country": [
            "JP"

        ],
        "site": "ABC"
    }
}
]

Example:

function list_ok(result) {
alert("ok=" + JSON.stringify(result));
}

function list_ko(result) {
alert("ko=" + JSON.stringify(result));
}

var my_pattern = {"$and":[{"details.country":"FR"},{"details.country":{"$nin":["UK"]}}]};

col_another_sample.listCallback(my_pattern,{},list_ok,list_ko);

In this example, col_another_sample is the ID of the collection. The query retrieves all items from the collection whose country array contains "FR" and not "UK".

Sorting Items in a Report

In a Collection Report widget of a web interface, you can sort items.

In the Query field you can enter {"$query":YOUR PATTERN,"$orderby":{"FIELD TO SORT":1 FOR ASCENDING / -1 FOR DESCENDING}}

Example:

{"$query":{"first_name":"John"},"$orderby":{"state":-1}}

This query retrieves all items from the collection matching first_name=John ordered by state in descending order.

Applying Aggregations in a Report

In a Collection report widget of a web interface, you have two possibilities to apply an aggregation:

• Directly in the Query field of the Collection Report settings:

  [{ "$group" : { "_id" : { "State" : "$state" }, "Total" : { "$sum" : "$nb_of_cars" } } }, { "$project" : { "STATE" : "$_id.State", "TOTAL_CARS" : "$Total" } } ]

• Using JavaScript:

  var my_pipelines = [{
  "$group" : {
  "_id" : {
  "State" : "$state"
  },
  "Total" : {
  "$sum" : "$nb_of_cars"
  }
  }
  }, {
  "$project" : {
  "STATE" : "$_id.State",
  "TOTAL_CARS" : "$Total"
  }
  }
  ];
  
  id_report.setAggregate(JSON.stringify(my_pipelines));
  id_report.refresh();
  

In both cases, all fields in the $project pipeline must be configured in the report columns definition:

The aggregated report will look like this: