×

Please give details of the problem

Skip to content

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.

Table of Contents

Filtering By Regular Expressions

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

The i option means "case-insensitive".

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
[{
    "details": {
        "country": [
            "FR",
            "UK",
            "USA"
        ],
        "site": "ABC"
    }
},
{
    "details": {
        "country": [
            "FR",
            "ES",
            "IT"
        ],
        "site": "ABC"
    }
},
{
    "details": {
        "country": [
            "JP"

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

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
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:

1
{"$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:

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
    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: