Receiving webhook with array of objects. Bug or not supported?

[JonL]

Don’t know if this is a bug, not supported or needs a workaround.

I am receiving an array of objects on the root. When I console.log on the server $_POST I can see it’s indeed [{},{},…] but when I want to save the result directly into DB it will open and close curly brackets and stringify the objects inside the array. So it will save {“stringified objects”}.

Is there any way to pass directly the array to the DB?

Sample of response.

[
  {
    "email": "example@test.com",
    "timestamp": 1513299569,
    "smtp-id": "<14c5d75ce93.dfd.64b469@ismtpd-555>",
    "event": "processed",
    "category": "cat facts",
    "sg_event_id": "sg_event_id",
    "sg_message_id": "sg_message_id"
  },
  {
    "email": "example@test.com",
    "timestamp": 1513299569,
    "smtp-id": "<14c5d75ce93.dfd.64b469@ismtpd-555>",
    "event": "deferred",
    "category": "cat facts",
    "sg_event_id": "sg_event_id",
    "sg_message_id": "sg_message_id",
    "response": "400 try again later",
    "attempt": "5"
  }
]

If I use the above result as a test value in the query it will save correctly into the jsonb PG field.

[George]

I’m not sure that parameters handle JSON objects as Test Value.
Maybe @patrick will check the JSON fields support in general. I think if the JSON is just a long string that it should work.

[patrick]

Try adding the test value as an expression, just place {{ and }} around it.

[JonL]

My problem is not with the test value functionality. That is actually working correctly. My problem is that the response sent by the server is an array of objects and before saving it into the database SC is parsing it in some manner that creates a wrong object.

Webhook response:
[{},{},{}…]

$_POST:
[{},{},{}…]

:P1 ($_POST):
{"{},{},{}"} aka { stringified objects comma separated }

Here a real sample of what’s being stored.

{"{\"email\":\"example@test.com\",\"timestamp\":1603142280,\"smtp-id\":\"<14c5d75ce93.dfd.64b469@ismtpd-555>\",\"event\":\"processed\",\"category\":[\"cat facts\"],\"sg_event_id\":\"LXFVSVZNuocjjq-BgQdARQ==\",\"sg_message_id\":\"14c5d75ce93.dfd.64b469.filter0001.16648.5515E0B88.0\"}","{\"email\":\"example@test.com\",\"timestamp\":1603142280,\"smtp-id\":\"<14c5d75ce93.dfd.64b469@ismtpd-555>\",\"event\":\"deferred\",\"category\":[\"cat facts\"],\"sg_event_id\":\"L5A9Ql8_a0-h0rFh5dEV2A==\",\"sg_message_id\":\"14c5d75ce93.dfd.64b469.filter0001.16648.5515E0B88.0\",\"response\":\"400 try again later\",\"attempt\":\"5\"}"}

This will produce errors as it’s not a valid object. Not even a stringified valid object. Those two curly brackets at the beginning and end are messing up with the response raw data.

I need to store in the DB the webhook response as is. In the appropiate json type column.

EDIT: Worth noting that I am using a custom query.

[sid]

Had an issue with storing RAW JSON in DB about an year ago.

Store API Response JSON in DB Table

Hi, I need to store the JSON response that I get from an API server action as value of one of the fields in my DB table. I tried setting the API’s JSON response in the insert/update database server action like {{api1.data}}, but its throwing an error. I don’t see any option to convert the JSON response to string before saving it in DB either. I have other use case where I store JSON value coming from client side UI in the DB, and that works fine because I stringify that JSON before sending i…

Not sure if this has been improved in the future updates as I had created a workaround at that time, and haven’t needed this since then.
If there could be a solution, it would be great.

[JonL]

I am assuming the problem lays in the parseValue that is called by parseSQL that doesn’t account for an array of objects.

For what I see in the code a json object would be treated correctly now. Maybe there was an update in between for that.