JSON match string parse

Hi folks,

I have a simple string received via IMAP from an email and containing a var {{ $json["text"] }} which looks like:

...from xxx LTD for x,xxx.xxCURRENCY issued 08/06/2020 and due by 01/07/2021 see...

I’d like to extract given substrings like x,xxx.xxCURRENCY which could be something like 1,000.00€ or 300.00USD and the relative dates preceded by issued ... and due by ....

I started to use something like: {{ $json["text"].match(/by \s*(\d+)/).toString() }} but with not much success, I get stuff like 01,01 for the dates and similar. Any ideas how I could achieve this and is {{ $json["text"].match(<whatever>).toString() }} the best way to do it?

Thanks for hints guys;)

Not as elegant as a regular expression, but the function node below should do it.

const results = []

for (const item of items) {
  const issue = item.json.text.split('issued')
  results.push({
    json: {
        money: issue[0].split(' ')[issue[0].split(' ').length - 2],
        date: issue[1].split(' ')[1],
        dueBy: issue[1].split('by')[1].split(' ')[1]
    }
  })
}

return results
Example workflow
{
  "nodes": [
    {
      "parameters": {},
      "name": "Start",
      "type": "n8n-nodes-base.start",
      "typeVersion": 1,
      "position": [
        240,
        300
      ]
    },
    {
      "parameters": {
        "functionCode": "return [\n  {\n    json: {\n      text: '...from xxx LTD for 1,000.00€ issued 08/06/2020 and due by 01/07/2021 see...'\n    }\n  },\n    {\n    json: {\n      text: '...from xxx LTD for 300.00USD issued 08/06/2020 and due by 01/07/2021 see...'\n    }\n  }\n]"
      },
      "name": "Function",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        540,
        300
      ],
      "notesInFlow": true,
      "notes": "Mockup data"
    },
    {
      "parameters": {
        "functionCode": "const results = []\n\nfor (const item of items) {\n  const issue = item.json.text.split('issued')\n  results.push({\n    json: {\n        money: issue[0].split(' ')[issue[0].split(' ').length - 2],\n        date: issue[1].split(' ')[1],\n        dueBy: issue[1].split('by')[1].split(' ')[1]\n    }\n  })\n}\n\nreturn results"
      },
      "name": "Function1",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        760,
        300
      ]
    }
  ],
  "connections": {
    "Start": {
      "main": [
        [
          {
            "node": "Function",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Function": {
      "main": [
        [
          {
            "node": "Function1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}
1 Like

Top, thanks, will try this out asap;)

@RicardoE105 any idea how to “format” x,xxx.xxCURRENCY into xxxx.xx which could be compatible with the Airtable currency field? Thanks;)

I found this Converting a number to a string which could be useful

Try it like this:

const results = []

for (const item of items) {
  const issue = item.json.text.split('issued')
  results.push({
    json: {
        money: issue[0].split(' ')[issue[0].split(' ').length - 2],
        date: issue[1].split(' ')[1],
        dueBy: issue[1].split('by')[1].split(' ')[1],
        moneyParsed: parseFloat(issue[0].split(' ')[issue[0].split(' ').length - 2].replace('USD', '').replace('EUR').replace('€', '').replace(/,/g, ''))
    }
  })
}

return results

Will try, thanks;)

works, thanks;)

1 Like