Rewriting JSON to Tabular Format

Is there a generic way of rewriting JSON to tabular format? For example, I want my Error workflow to inject the data into a PostgreSQL db. Is there an easy way to go from:

[
    {
        "execution": {
            "id": "231",
            "url": "https://n8n.example.com/execution/231",
            "retryOf": "34",
            "error": {
                "message": "Example Error Message",
                "stack": "Stacktrace"
            },
            "lastNodeExecuted": "Node With Error",
            "mode": "manual"
        },
        "workflow": {
            "id": "1",
            "name": "Example Workflow"
        }
    }
]

To something like:

execution_id, execution_url, execution_retryOf, execution_error_message,  execution_error_stack, etc...
231, https://n8n.example.com/execution/231, 34, Example Error Message", Stacktrace, etc...

I don’t really care about how it handles the multi level flattening in terms of naming convention as I control the DB, but having this functionality in a core node rather than having to write Function nodes for this would be great.

Thanks!

Ah yes the easiest would be to simply use the Set-Node and activate “Keep Only Set”. You can then add the properties you want and select the values to use from the JSON with the Expression-Editor.

Since the Error Trigger node can’t be selected in the Expression-Editor this doesn’t work super well. Even if I mock this up in the Function node like so:

{
  "nodes": [
    {
      "parameters": {
        "functionCode": "let example = [];\nexample.push(\n    {\"json\": {\n        \"execution\": {\n            \"id\": \"231\",\n            \"url\": \"https://n8n.example.com/execution/231\",\n            \"retryOf\": \"34\",\n            \"error\": {\n                \"message\": \"Example Error Message\",\n                \"stack\": \"Stacktrace\"\n            },\n            \"lastNodeExecuted\": \"Node With Error\",\n            \"mode\": \"manual\"\n        },\n        \"workflow\": {\n            \"id\": \"1\",\n            \"name\": \"Example Workflow\"\n        }\n    }\n    })\nreturn example;"
      },
      "name": "Function",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        400,
        20
      ]
    }
  ],
  "connections": {}
}

then the Expression-Editor doesn’t allow me to pick against it. I guess I could code against it in the Expression-Editor, but this seems like an area where core improvements to the Error Trigger may be a better approach. I am currently on Day 3 of n8n and my ability to debug failures is by far the biggest pain point I am hitting.

Even without doing this mapping, I tried to take a shortcut and map the Error node into PostgreSQL using JSON columns like so:

{
  "nodes": [
    {
      "parameters": {
        "table": "error",
        "columns": "execution, workflow"
      },
      "name": "Postgres",
      "type": "n8n-nodes-base.postgres",
      "typeVersion": 1,
      "position": [
        950,
        210
      ],
      "credentials": {
        "postgres": "n8n DB"
      }
    }
  ],
  "connections": {}
}

And it doesn’t work. This makes me think that the structure that I am trying to map into the PostgreSQL node is wrong.

Sorry do not understand what you mean with “Since the Error Trigger node can’t be selected in the Expression-Editor this doesn’t work super well.”.

You can simply press the “play” button on the node and it should then load example-data which you can select like the data on any other node.

You’re absolutely right, I thought I had tried that and it returned nothing. I must have done something wrong. Then this works exactly the way you described in the Expression-Editor.

In case anyone else wants a Set node example, mine ended up looking like so:

  "nodes": [
    {
      "parameters": {
        "keepOnlySet": true,
        "values": {
          "number": [
            {
              "name": "execution_id",
              "value": "={{$node[\"Error Trigger\"].json[\"execution\"][\"id\"]}}"
            },
            {
              "name": "execution_retry_of",
              "value": "={{$node[\"Error Trigger\"].json[\"execution\"][\"retryOf\"]}}"
            },
            {
              "name": "workflow_id",
              "value": "={{$node[\"Error Trigger\"].json[\"workflow\"][\"id\"]}}"
            }
          ],
          "string": [
            {
              "name": "execution_url",
              "value": "={{$node[\"Error Trigger\"].json[\"execution\"][\"url\"]}}"
            },
            {
              "name": "execution_error_message",
              "value": "={{$node[\"Error Trigger\"].json[\"execution\"][\"error\"][\"message\"]}}"
            },
            {
              "name": "execution_error_stack",
              "value": "={{$node[\"Error Trigger\"].json[\"execution\"][\"error\"][\"stack\"]}}"
            },
            {
              "name": "execution_last_node_executed",
              "value": "={{$node[\"Error Trigger\"].json[\"execution\"][\"lastNodeExecuted\"]}}"
            },
            {
              "name": "execution_mode",
              "value": "={{$node[\"Error Trigger\"].json[\"execution\"][\"mode\"]}}"
            },
            {
              "name": "workflow_name",
              "value": "={{$node[\"Error Trigger\"].json[\"workflow\"][\"name\"]}}"
            }
          ]
        },
        "options": {
          "dotNotation": false
        }
      },
      "name": "Set",
      "type": "n8n-nodes-base.set",
      "typeVersion": 1,
      "position": [
        770,
        290
      ]
    }
  ],
  "connections": {}
}

I am not sure why the Error Trigger sends the execution.retryOf and the workflow.id values as strings, and the Set node doesn’t convert them into numbers but :man_shrugging:

Is there any way to troubleshoot why a Workflow would fail but the Error Workflow would not be triggered?

Hm, did never hear about that kind of problem before.

Did the workflow with the “Error Trigger” node really got set as Error-Workflow via the Workflow-Settings on the failing workflow? The Error-Workflow has to be set on each workflow separately.

Ah and about the Set-Node and numbers. It does not automatically do a cast on the values which get set via expression. So if the expression returns something else it would simply set it. So you would then have to do a parseInt.

Sharing the fixed Set Node example in case anyone else stumbles across this:

  "nodes": [
    {
      "parameters": {
        "keepOnlySet": true,
        "values": {
          "number": [
            {
              "name": "execution_id",
              "value": "={{$node[\"Error Trigger\"].json[\"execution\"][\"id\"]}}"
            },
            {
              "name": "execution_retry_of",
              "value": "={{parseInt($node[\"Error Trigger\"].json[\"execution\"][\"retryOf\"])}}"
            },
            {
              "name": "workflow_id",
              "value": "={{parseInt($node[\"Error Trigger\"].json[\"workflow\"][\"id\"])}}"
            }
          ],
          "string": [
            {
              "name": "execution_url",
              "value": "={{$node[\"Error Trigger\"].json[\"execution\"][\"url\"]}}"
            },
            {
              "name": "execution_error_message",
              "value": "={{$node[\"Error Trigger\"].json[\"execution\"][\"error\"][\"message\"]}}"
            },
            {
              "name": "execution_error_stack",
              "value": "={{$node[\"Error Trigger\"].json[\"execution\"][\"error\"][\"stack\"]}}"
            },
            {
              "name": "execution_last_node_executed",
              "value": "={{$node[\"Error Trigger\"].json[\"execution\"][\"lastNodeExecuted\"]}}"
            },
            {
              "name": "execution_mode",
              "value": "={{$node[\"Error Trigger\"].json[\"execution\"][\"mode\"]}}"
            },
            {
              "name": "workflow_name",
              "value": "={{$node[\"Error Trigger\"].json[\"workflow\"][\"name\"]}}"
            }
          ]
        },
        "options": {
          "dotNotation": false
        }
      },
      "name": "Set",
      "type": "n8n-nodes-base.set",
      "typeVersion": 1,
      "position": [
        770,
        290
      ]
    }
  ],
  "connections": {}
}

The Error workflow which works while manually testing, but not from another workflow failure is:

{
  "nodes": [
    {
      "parameters": {},
      "name": "Error Trigger",
      "type": "n8n-nodes-base.errorTrigger",
      "typeVersion": 1,
      "position": [
        480,
        200
      ]
    },
    {
      "parameters": {
        "table": "error",
        "columns": "execution, workflow"
      },
      "name": "Postgres",
      "type": "n8n-nodes-base.postgres",
      "typeVersion": 1,
      "position": [
        950,
        210
      ],
      "credentials": {
        "postgres": "n8n DB"
      }
    }
  ],
  "connections": {
    "Error Trigger": {
      "main": [
        [
          {
            "node": "Postgres",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

And I know the other workflow is failing:


Based on some searching on community, this seems like it may be an OOM?

The previous node returns 9297 rows (552KB) of email addresses. This Function Item node strips out the part that isn’t the domain:

{
  "nodes": [
    {
      "parameters": {
        "functionCode": "let email = item[\"technicalContactEmail\"];\nlet domain = email.split(\"@\")[1];\nreturn {'domain': domain, 'type': \"customer\"};"
      },
      "name": "ParseTCEmail",
      "type": "n8n-nodes-base.functionItem",
      "typeVersion": 1,
      "position": [
        640,
        200
      ],
      "alwaysOutputData": false,
      "continueOnFail": true
    }
  ],
  "connections": {}
}

So I am not sure why this would cause an OOM, and I can’t figure out how to troubleshoot this further since the Error Workflow isn’t being called even though it is configured:

1 Like

Will try to have a look the next days. It is possible that the code which executes the error workflow is not getting called when the process crashes. That should obviously be fixed.

Anyway but even then would it still not display any helpful information because if the process crashes, it would not get any data from it as it only sends the information to the main process once the workflow finished. In this case it crashes and so it never gets any data. Not sure right now if this behaviour should really be changed though. The reason is that it would increase the memory consumption a lot which would so increase again the chance of such errors happening. A “solution” would be to save that data directly to a DB or disc but that would also increase the overhead a lot…

Anyway can also not understand right why it would crash because of 500KB in the first place.

Btw. a Merge-Node should always have data connected to both inputs. And if you connect multiple nodes to the same input it maybe does not behave like you expect. In the wait case (like you have it above) would it wait only till it receives data from one of the nodes and then proceed, not for all. So you would probably want two Merge-Nodes. One after the other.

1 Like

I was able to get around this by switching from Function Item to Function node:

{
  "nodes": [
    {
      "parameters": {
        "functionCode": "var i;\nlet domains = []\nfor (i = 0; i < items.length; i++) {\n  let domain =  items[i].json.technicalContactEmail.split(\"@\")[1];\n  if (!domains.some(e => e.domain === domain)){\n    domains.push({\n        json: {\n          'domain': domain,\n          'type':'customer'\n        }\n      });\n  }\n}\n\nreturn domains;\n"
      },
      "name": "ParseTCEmail",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        1090,
        260
      ]
    }
  ],
  "connections": {}
}

Still need to figure out how to de-dupe.

Updated Merge-Node copy at https://github.com/n8n-io/n8n/pull/543 to clarify this.