SQS to S3 (Parse body to JSON)

Hi All - Apologies if this the incorrect channel. I’m new to Airbyte so currently building our first connection between SQS and S3 and my question is around the body of the message being a string - Is there a way to parse the body to JSON. We have previously used another solution http://Tray.io where the connector performed this functionality or has anybody any advice on how to perform this pre-S3

TIA

Hey can you help us understand what is the current process if you are using source (source-amazon-sqs) and as destination (destination-s3)?

Yes of course.

We have a 3rd party that post message to a SQS queue which contains data we use in our Snowflake DW.

Within the current workflow with Tray it pulls the message down from SQS with any attributes we need but also parses the body of the message into JSON.

If you pull a message from SQS via the Airbyte connector or using AWS CLI then the body of the message is a string which is escaped

see example

{
    "_airbyte_ab_id": "01d47543-e26f-4c2e-b7c7-8ae4269a9baf",
    "_airbyte_emitted_at": 1653388090000,
    "_airbyte_data": {
        "id": "8ed6e48e-78d6-4062-93a5-cac297ace902",
        "body": "{\"externalId\":\"fc3a6f4c-fd0d-4fc4-9ff7-3b6596031b13\",\"payload\":{\"_id\":\"6288ad66ad298506de94d15d\",\"createdAt\":\"2022-05-21T09:14:33.251Z\",\"date\":\"2022-05-21T08:02:48.000Z\",\"deleted\":false,\"distance\":5048,\"duration\":1723,\"isValid\":true,\"points\":51,\"type\":\"Running\",\"uid\":\"612884acc9777d0007976e11\",\"updatedAt\":null,\"timezone\":1,\"provider\":\"garmin\",\"climb\":50,\"descent\":58,\"deletedAt\":null},\"resourceType\":\"activity\",\"status\":\"CREATED\"}",
        "attributes": null
    }
}

I was wondering if this could be completed before it gets posted to a S3 bucket.

Hey I got that. we don’t have a out of box solution but there are 2 ways you can do this.

  1. You can use our API to change the connection catalog https://airbyte-public-api-docs.s3.us-east-2.amazonaws.com/rapidoc-api-docs.html#post-/v1/connections/update and change the body from string to object and give the respective keys so that in normalisation we can create a new table for this.
  2. Other way is you can handle this in custom DBT