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
1 |
|
The i
option means "case-insensitive".
Example:
1 2 3 4 5 6 7 8 9 10 11 12 |
|
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 |
|
Example:
1 2 3 4 5 6 7 8 9 10 11 |
|
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 |
|
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:
Please give details of the problem