Help needed for inserting of null values

Hi there,
How can I get the node to treat null values as null so I can insert it into a DB? I am getting errors when trying to run a REPLACE INTO statement because the previous result contains null values and the node doesn’t output the value as null

Can you please post an example as I currently do not understand where the problem is. It sounds like that you are saying that nodes can not output properties with the value null. But they can. So an example to reproduce the exact problem you are having would be helpful.

{
  "nodes": [
    {
      "parameters": {
        "functionCode": "items[0].json.myVariable = null;\nreturn items;"
      },
      "name": "Function",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        400,
        300
      ]
    }
  ],
  "connections": {}
}

Hi @jan
I have the following workflow set up, but as you can see from the screenshot the output in property “made_by_userid” returns null but when using the node instead of writing null it is just empty. I have tried to wrap it around in quotes but as the column is an integer it doesn’t allow for empty string only null or integer values.

{
  "name": "events",
  "nodes": [
    {
      "parameters": {},
      "name": "Start",
      "type": "n8n-nodes-base.start",
      "position": [
        -200,
        300
      ],
      "typeVersion": 1
    },
    {
      "parameters": {},
      "name": "End1",
      "type": "n8n-nodes-base.noOp",
      "position": [
        1520,
        240
      ],
      "typeVersion": 1
    },
    {
      "parameters": {
        "conditions": {
          "string": [],
          "boolean": [
            {
              "value1": true,
              "value2": "={{$node[\"SplitInBatches1\"].context[\"noItemsLeft\"]}}"
            }
          ]
        }
      },
      "name": "IF1",
      "type": "n8n-nodes-base.if",
      "position": [
        1250,
        250
      ],
      "typeVersion": 1
    },
    {
      "parameters": {
        "operation": "executeQuery",
        "query": "SELECT\nlog_id, \nlogged_at, \nevent_id, \nmade_by_userid\nFROM\nevents\nLIMIT 10"
      },
      "name": "MySQL",
      "type": "n8n-nodes-base.mySql",
      "position": [
        530,
        430
      ],
      "typeVersion": 1,
      "credentials": {
        "mySql": "test"
      }
    },
    {
      "parameters": {
        "batchSize": 1,
        "options": {}
      },
      "name": "SplitInBatches",
      "type": "n8n-nodes-base.splitInBatches",
      "position": [
        990,
        460
      ],
      "typeVersion": 1
    },
    {
      "parameters": {
        "operation": "executeQuery",
        "query": "=REPLACE INTO events (log_id,logged_at,event_id,made_by_userid) \nVALUES (\n{{$node[\"SplitInBatches\"].json[\"log_id\"]}},\n\"{{$node[\"SplitInBatches\"].json[\"logged_at\"]}}\",\n{{$node[\"SplitInBatches\"].json[\"event_id\"]}},\n{{$node[\"SplitInBatches\"].json[\"made_by_userid\"]}})"
      },
      "name": "MySQL2",
      "type": "n8n-nodes-base.mySql",
      "position": [
        1450,
        440
      ],
      "typeVersion": 1,
      "credentials": {
        "mySql": "test2"
      }
    },
    {
      "parameters": {
        "value": "={{$node[\"MySQL\"].json[\"logged_at\"]}}",
        "dataPropertyName": "logged_at",
        "custom": true,
        "toFormat": "YYYY-MM-DD HH:MM:SS",
        "options": {}
      },
      "name": "Date & Time",
      "type": "n8n-nodes-base.dateTime",
      "position": [
        680,
        430
      ],
      "typeVersion": 1
    }
  ],
  "connections": {
    "IF1": {
      "main": [
        [
          {
            "node": "End1",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "SplitInBatches",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "MySQL": {
      "main": [
        [
          {
            "node": "Date & Time",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Start": {
      "main": [
        [
          {
            "node": "MySQL",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "MySQL2": {
      "main": [
        [
          {
            "node": "IF1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Date & Time": {
      "main": [
        [
          {
            "node": "SplitInBatches",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "SplitInBatches": {
      "main": [
        [
          {
            "node": "MySQL2",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  },
  "active": false,
  "settings": {
    "timezone": "Asia/Bangkok",
    "saveManualExecutions": true
  },
  "id": "1"
}

Ah OK. Now I understand. You mean in an expression. To add “null” there, you can do something like this:

{{ $node["SplitInBatches"].json["made_by_userid"] === null ? 'null' : '"' + $node["SplitInBatches"].json["made_by_userid"] + "'" }}

Thank you, worked like a charm.
Would be nice if there was either a node or a setting to define how data should be treated

Sure, great to hear!

Sorry do not understand. Can you please elaborate on how you would expect it to work.

Partially it would be great if the Mysql Node could be extended to also have the option for REPLACE INTO and not only Insert/Update/Query

What I was thinking would be to have a setting in the node like seen below that would basically do what you suggested above but for all columns.
I think this could be useful for not only Mysql but any node that accept input.
Hope this clears it up a bit

{{ $node["SplitInBatches"].json["made_by_userid"] === null ? 'null' : '"' + $node["SplitInBatches"].json["made_by_userid"] + "'" }}