Help with using multiple output into a mysql query

Hi,

I am querying sendgrid to get all the bounces that are generated in a day and am getting the following json:

[
  {
    "created": 1658050606,
    "email": "[email protected]",
    "reason": "554 30 Sorry, your message to [email protected] cannot be delivered. This mailbox is disabled (554.30).",
    "status": "554"
  },
  {
    "created": 1658050598,
    "email": "[email protected]",
    "reason": "552 3 Requested mail action aborted, mailbox not found",
    "status": "552"
  },
  {
    "created": 1658050007,
    "email": "[email protected]",
    "reason": "552 1 Requested mail action aborted, mailbox not found",
    "status": "552"
  }
]

Then I am trying to use the mysql node to do an insert on all the email address and status from above.
Problem is I do not know how to iterate this so it goes one by one on the insert,

Here is an example of my workflow:

Thanks,

Mike

Hi @Surge.Media

You can use the item lists node to split the array and then you can easily push that data into the database.

Thanks @BramKnuever I have tried going through to docs on item list but am unable to get the implemetnation

Hi @Surge.Media

Not behind a pc right now. So not able to get you an example.
It should be fairly straightforward though.
Add the node. Select the option called split items . Enter the field name where you got the array.

Hey @BramKnuever

On what name should i split here, as they are seperate arrays (if my understanding is correct). So if i choose email, i get an error:

[
  [
    {
      "created": 1658050606,
      "email": "[email protected]",
      "reason": "554 30 Sorry, your message to [email protected] cannot be delivered. This mailbox is disabled (554.30).",
      "status": "554"
    },
    {
      "created": 1658050598,
      "email": "[email protected]",
      "reason": "552 3 Requested mail action aborted, mailbox not found",
      "status": "552"
    },
    {
      "created": 1658050007,
      "email": "[email protected]",
      "reason": "552 1 Requested mail action aborted, mailbox not found",
      "status": "552"
    }
  ]
]

Hi @Surge.Media, seeing you are using the HTTP Request node you might want to give the Split Into Items option a go:

image

This should return each item on its own without the need for an additional node.

1 Like

Hi @Surge.Media

Your data should be listed in a field when receiving it from the node before.
I see you are using HTTP request node now. (didn’t see that on my phone)
So @MutedJam has given you an easier solution. :slight_smile:

1 Like

Hi @MutedJam - thanks that worked.

Now onto my next issue, inside the MySQL node I am running an update execution. Inside my database I have the email field set to UNIQUE so that there are not duplicate entries. When I execute this node it stops on the first entry if there is already an entry for the first email address.

I have checked out the options and am not able to find out how to make it continue to iterate on all the other results.

My first guess was the continue on fail option. But this doesn’t continue executing other items within the same node If I read the info correctly.
Maybe this option does exactly that:
image
Otherwise I think you will need to get the records and remove duplicates first before sending them to mysql.
Not sure though, as I never really use MySQL.

Thanks @BramKnuever - this option of ignore does not exist in the mysql node.
I have tried the following options but alas still not acting the way I would want it to:
image

Appreciate all the help so far!!

Hi @Surge.Media

This option is not with these options it is with the parameters:

1 Like

Thanks that did it. My issue was I was using an execute command and not an insert command!

2 Likes