Collect workflows that reference a subworkflow

workflow.json

{
  "nodes": [
    {
      "parameters": {},
      "type": "n8n-nodes-base.manualTrigger",
      "typeVersion": 1,
      "position": [
        0,
        0
      ],
      "id": "da44edde-aaaa-4e5a-aaf7-6b9729db3669",
      "name": "When clicking ‘Execute workflow’"
    },
    {
      "parameters": {
        "filters": {},
        "requestOptions": {}
      },
      "type": "n8n-nodes-base.n8n",
      "typeVersion": 1,
      "position": [
        208,
        0
      ],
      "id": "dcd4d8ce-8b63-41a9-b0e2-32518b5bfebe",
      "name": "Get many workflows",
      "credentials": {
        "n8nApi": {
          "id": "ojEG6wNIOkrUowjR",
          "name": "n8n account"
        }
      }
    },
    {
      "parameters": {
        "jsCode": "const executions = [];\n\nfor (const item of $('Get many workflows').all()) {\n  for(const node of item.json.nodes) {\n    if(node.type === \"n8n-nodes-base.executeWorkflow\") {\n      executions.push({ \n        workflow: {\n          id: item.json.id,\n          name: item.json.name,\n          active: item.json.active\n        },\n        node \n      });\n    }\n  }\n}\n\nreturn executions;"
      },
      "type": "n8n-nodes-base.code",
      "typeVersion": 2,
      "position": [
        752,
        0
      ],
      "id": "bbc4d286-6930-4359-a39e-5edb183ab475",
      "name": "Find subworkflow executions"
    },
    {
      "parameters": {
        "operation": "upsert",
        "dataTableId": {
          "__rl": true,
          "value": "0BGIGnteoCtE7ZiF",
          "mode": "list",
          "cachedResultName": "Subworkflow executions"
        },
        "matchType": "allConditions",
        "filters": {
          "conditions": [
            {
              "keyName": "subWorkflowId",
              "keyValue": "={{ $json.node.parameters.workflowId.value }}"
            },
            {
              "keyName": "parentWorkflowId",
              "keyValue": "={{ $json.workflow.id }}"
            }
          ]
        },
        "columns": {
          "mappingMode": "defineBelow",
          "value": {
            "subWorkflowExists": "={{ !!$('Workflow metadata').all().find(({ json }) => json.id === $json.node.parameters.workflowId.value) }}",
            "subWorkflowId": "={{ $json.node.parameters.workflowId.value }}",
            "subWorkflowName": "={{ \n$('Workflow metadata').all().find(({ json }) => json.id === $json.node.parameters.workflowId.value)?.json?.name ?? null\n}}",
            "parentWorkflowId": "={{ $json.workflow.id }}",
            "parentWorkflowName": "={{ $json.workflow.name }}",
            "parentWorkflowIsActive": "={{ $json.workflow.active }}"
          },
          "matchingColumns": [],
          "schema": [
            {
              "id": "subWorkflowId",
              "displayName": "subWorkflowId",
              "required": false,
              "defaultMatch": false,
              "display": true,
              "type": "string",
              "readOnly": false,
              "removed": false
            },
            {
              "id": "subWorkflowName",
              "displayName": "subWorkflowName",
              "required": false,
              "defaultMatch": false,
              "display": true,
              "type": "string",
              "readOnly": false,
              "removed": false
            },
            {
              "id": "subWorkflowExists",
              "displayName": "subWorkflowExists",
              "required": false,
              "defaultMatch": false,
              "display": true,
              "type": "boolean",
              "readOnly": false,
              "removed": false
            },
            {
              "id": "parentWorkflowId",
              "displayName": "parentWorkflowId",
              "required": false,
              "defaultMatch": false,
              "display": true,
              "type": "string",
              "readOnly": false,
              "removed": false
            },
            {
              "id": "parentWorkflowName",
              "displayName": "parentWorkflowName",
              "required": false,
              "defaultMatch": false,
              "display": true,
              "type": "string",
              "readOnly": false,
              "removed": false
            },
            {
              "id": "parentWorkflowIsActive",
              "displayName": "parentWorkflowIsActive",
              "required": false,
              "defaultMatch": false,
              "display": true,
              "type": "boolean",
              "readOnly": false,
              "removed": false
            }
          ],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {}
      },
      "type": "n8n-nodes-base.dataTable",
      "typeVersion": 1,
      "position": [
        992,
        0
      ],
      "id": "7f67145c-8a17-4d59-94df-c511bedd28d5",
      "name": "Upsert row(s)"
    },
    {
      "parameters": {
        "events": [
          "update",
          "activate",
          "init"
        ]
      },
      "type": "n8n-nodes-base.n8nTrigger",
      "typeVersion": 1,
      "position": [
        0,
        -176
      ],
      "id": "c41d00cd-500f-4291-9d76-884ddaf6a570",
      "name": "n8n Trigger"
    },
    {
      "parameters": {
        "rule": {
          "interval": [
            {
              "field": "hours",
              "hoursInterval": 6
            }
          ]
        }
      },
      "type": "n8n-nodes-base.scheduleTrigger",
      "typeVersion": 1.2,
      "position": [
        0,
        192
      ],
      "id": "552f8eef-3f6f-4a0d-a95e-f69fc5fcfb1b",
      "name": "Schedule Trigger"
    },
    {
      "parameters": {
        "assignments": {
          "assignments": [
            {
              "id": "c7d4a445-8949-4820-bd6f-79d793d42436",
              "name": "id",
              "value": "={{ $json.id }}",
              "type": "string"
            },
            {
              "id": "40c58900-3cfc-4683-835a-0ac91912d8c5",
              "name": "name",
              "value": "={{ $json.name }}",
              "type": "string"
            }
          ]
        },
        "options": {}
      },
      "type": "n8n-nodes-base.set",
      "typeVersion": 3.4,
      "position": [
        464,
        0
      ],
      "id": "2368bcb2-7772-491a-8be1-60d4304c59e8",
      "name": "Workflow metadata"
    },
    {
      "parameters": {
        "content": "Purely for performance reasons because we are going to map and filter through the list in the upsert",
        "height": 400,
        "width": 256
      },
      "type": "n8n-nodes-base.stickyNote",
      "typeVersion": 1,
      "position": [
        384,
        -160
      ],
      "id": "b1af3564-d20a-468b-a3ea-4bee182afd91",
      "name": "Sticky Note"
    }
  ],
  "connections": {
    "When clicking ‘Execute workflow’": {
      "main": [
        [
          {
            "node": "Get many workflows",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Get many workflows": {
      "main": [
        [
          {
            "node": "Workflow metadata",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Find subworkflow executions": {
      "main": [
        [
          {
            "node": "Upsert row(s)",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "n8n Trigger": {
      "main": [
        [
          {
            "node": "Get many workflows",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Schedule Trigger": {
      "main": [
        [
          {
            "node": "Get many workflows",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Workflow metadata": {
      "main": [
        [
          {
            "node": "Find subworkflow executions",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  },
  "pinData": {},
  "meta": {
    "templateCredsSetupCompleted": true,
    "instanceId": "foobar"
  }
}