How can I use MongoDB $facet in Rierino

We are using the low code platform Rierino for developing our backend microservices. One of our current requirements is to perform facet search on products and content on our MongoDB database.

Here is a minimal example that I am trying to build using the Query Builder:

db.getCollection("product").aggregate([
    {$match: { "data.brand": "nike" }},
    {$facet: {
        "categories":[
            {$match: { "data.inFacet": true }},
            {$group: {
                "_id": "$data.category",
                "count": {$sum: 1}
            }}
        ]
    }}
])

I found 2 ways we can implement this:

Using "Command" query type, which allows me to enter full text queries in MongoDB syntax
Using "Pipeline" query type, which allows me to create aggregate queries, where I can add $facet as a JSON step

However, I would like to make use of the dynamic filtering capabilities of Rierino as different facets have different where conditions and I couldn't figure out a way to do it well with either option. Command query allows using templates and Pipeline would allow injecting JSON queries we build with a transform step, but neither would be as easy to maintain as the rest of our queries.

Is there a way to create facet search within Rierino using the visual Query Builder instead of these text & JSON based configurations?

Solution

You can perform MongoDB facet search in Rierino as follows:

Select "BUNDLE" from the dropdown on query "DEFINITION" tab. This will add a new "BUNDLES" tab to the query design screen.
Add an "AGGREGATION" type query as a bundle in this new tab, with your data.inFacet condition and returning aggregation fields data.category as "GROUP BY" and _id as "COUNT" (or 1 as "SUM" if you prefer)

Bundle query type in Rierino serves this exact use case, in MongoDB it automatically translates your query to facet pipelines. Where condition in your main query is applied to all facets and additional conditions like your data.inFacet can be added to where condition of your bundled queries.

Below you can find the full configuration which you can import as a file to load this query using the visual query designer.

Notes:

I added a variable injection for the brand filter (i.e. %%brand%%), assuming that would be a value you pass as an input parameter.
I set the limit for main query as 1 to avoid returning too many rows in response.

{
    "name": "Product Facet Example",
    "status": "A",
    "type": "BUNDLE",
    "platform": "MONGODB",
    "description": "Example query returning number of products per query as a bundle",
    "from": "product",
    "where": {
        "category": "SIMPLE",
        "command": "data.brand",
        "operator": "EQ",
        "value": "%%brand%%"
    },
    "limit": "1",
    "bundles": [
        {
            "name": "Categories",
            "type": "AGGREGATION",
            "as": "categories",
            "where": {
                "category": "SIMPLE",
                "command": "data.inFacet",
                "operator": "TRUE"
            },
            "fields": [
                {
                    "type": "GROUP",
                    "expression": "data.category",
                    "target": "category"
                },
                {
                    "type": "COUNT",
                    "expression": "_id",
                    "target": "count"
                }
            ]
        }
    ]
}