How to insert JSON array into Postgres column without it thinking its text

Questions
,
1

I’m using an API to return a JSON array (example below) however when i try to pass this in an insert the results element to a Postgres DB in errors with the expression is TEXT.

[
  {
    "status": "COMPLETE",
    "results": [
      {
        "from": {
          "id": "7428563627"
        },
        "to": [
          {
            "id": "6022001",
            "type": "company_to_contact"
          }
        ]
      }
    ],
    "startedAt": "2021-12-09T08:57:02.607Z",
    "completedAt": "2021-12-09T08:57:02.619Z",
    "uuid": "bef1a58c-127d-440a-8c3a-8f739533085e",
    "processed_flag": 0,
    "batch_id": 100000002
  },
  {
    "status": "COMPLETE",
    "results": [],
    "numErrors": 1,
    "errors": [
      {
        "status": "error",
        "category": "OBJECT_NOT_FOUND",
        "subCategory": "crm.associations.NO_ASSOCIATIONS_FOUND",
        "message": "No contact is associated with company 7435569172.",
        "context": {
          "fromObjectId": [
            "7435569172"
          ],
          "fromObjectType": [
            "company"
          ],
          "toObjectType": [
            "contact"
          ]
        }
      }
    ],
    "startedAt": "2021-12-09T08:57:02.598Z",
    "completedAt": "2021-12-09T08:57:02.606Z",
    "uuid": "83962368-2b06-46ec-a24e-4b9e53c11119",
    "processed_flag": 0,
    "batch_id": 100000002
  },
  {
    "status": "COMPLETE",
    "results": [
      {
        "from": {
          "id": "7435757703"
        },
        "to": [
          {
            "id": "6025351",
            "type": "company_to_contact"
          }
        ]
      }
    ],
    "startedAt": "2021-12-09T08:57:02.607Z",
    "completedAt": "2021-12-09T08:57:02.615Z",
    "uuid": "7fe57ad2-3433-4ff4-afeb-be8482ff3009",
    "processed_flag": 0,
    "batch_id": 100000002
  }
]

Sorry i’m quite new to this is. Do i need to run a function against this first before inserting it in to the table.

If i change the column to text it loads but is un-useable because of the escape characters.

error received is

Problem executing workflow

There was a problem executing the workflow:
"column “results” is of type json but expression is of type text[]"

Many thanks in advance

2

Hi @messi198310, first of all welcome to the community :tada:

That’s a tough one - I was hoping I could simply cast the type here, but that doesn’t seem to work. Let me play around a bit and see if I can come up with a workaround for this.

3

Thanks @MutedJam.

Appreciate the welcome and the offer to investigate.

4

Okay, so converting the JSON into a string inside n8n seems to do the job for me. Here’s my workflow (you can just copy + paste it right into your n8n canvas):

Example Workflow

The first Function node just writes the example data you have provided into an input field. The second node in the flow (Function Item) then uses JSON.stringify() to convert the JSON value. The last Postgresql node is cool with the string value and correctly inserts it into my JSON field:

image
image921×711 36.5 KB

Also double-checked this using psql:

image
image1174×464 153 KB

Hope this works for you :slight_smile:

5

You my friend are a legend! Works a treat.

Thanks for your help

2 Likes