How to retrieve all submission data in multiple versions of a form from Formio to redash

[shuhaib-aot]

1. how to get all data by form id of a form and its all version
2. how to get all data by form path name and its all version

[shuhaib-aot]

Fetch form submission data by form path

{
    "collection": "forms",
    "aggregate": [
        {
            "$match": {
            "path": "dashboardcheck"
            }
        },
        {
            "$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
    }
}