Skip to main content
Version: 7.0.0

Sample Queries

Here are some sample queries that you can use to manage Form data.

Fetch form submission data by form path​

{
    "collection": "forms",
    "aggregate": [
        {
            "$match": {
            "path": "formpathname"
            }
        },
        {
            "$addFields": {
                "resultId": { "$cond": { 
                    "if": { "$ifNull": [ "$parentFormId",false]},
                    "then": "$parentFormId",
                    "else": "$_id"
                }}
            }
        },
        {
            "$lookup": {
            "from": "forms",
            "let": {"id": "$resultId"},
            "pipeline":[
                        {"$match": { "$expr": {"$or": [
                            {"$eq": ["$_id",{"$convert": {"input": "$$id","to": "objectId"}}]},
                            {"$eq": ["$parentFormId","$$id"]}
                            ]}
                        }},
                        {"$project": {"_id": 1}}
            ],
            "as": "ids"
            }
        },
        {"$unwind": "$ids"},
        {
            "$lookup": {
            "from": "submissions",
            "localField": "ids._id",
            "foreignField": "form",
            "as": "submissionsJoin"
            
                }
        },
        {"$unwind": "$submissionsJoin"},
        {
            "$group": {
                "_id": {
                "department": "$submissionsJoin.data.department"
                },
                "count": {"$sum": 1}
            }
        },
        {
            "$project": {
                "department": "$_id.department",
                "totalSubmissions": "$count"
            }
        }
    ]
}

Fetch form submission data by parent form id or form id​

Parent Form Id is the initial form id when the form created Form Id is specific for each form

when you use the parent form id then you will get all the submission data of this form and its versions​

{
    "collection": "forms",
    "aggregate": [
        {
            "$match": {
                "$or": [
                    {
                        "_id": {
                            "$oid": "formid"
                        }
                    },
                    {
                        "parentFormId": "formid"
                    }
                ]
            }
        },
        {
            "$lookup": {
                "from": "submissions",
                "localField": "_id",
                "foreignField": "form",
                "as": "submissionsJoin"
            }
        },
        {
            "$unwind": "$submissionsJoin"
        },
        {
            "$group": {
                "_id": {
                    "department": "$submissionsJoin.data.department"
                },
                "count": {
                    "$sum": 1
                }
            }
        },
        {
            "$project": {
                "department": "$_id.department",
                "totalSubmissions": "$count"
            }
        }
    ],
    "fields": {
        "department": 1,
        "totalSubmissions": 1
    }
}

image