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

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

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.

Thanks @MutedJam.

Appreciate the welcome and the offer to investigate.

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
{
  "nodes": [
    {
      "parameters": {},
      "name": "Start",
      "type": "n8n-nodes-base.start",
      "typeVersion": 1,
      "position": [
        240,
        300
      ]
    },
    {
      "parameters": {
        "functionCode": "return [{\n  json: {\n    input: [{\n        \"status\": \"COMPLETE\",\n        \"results\": [{\n            \"from\": {\n                \"id\": \"7428563627\"\n            },\n            \"to\": [{\n                \"id\": \"6022001\",\n                \"type\": \"company_to_contact\"\n            }]\n        }],\n        \"startedAt\": \"2021-12-09T08:57:02.607Z\",\n        \"completedAt\": \"2021-12-09T08:57:02.619Z\",\n        \"uuid\": \"bef1a58c-127d-440a-8c3a-8f739533085e\",\n        \"processed_flag\": 0,\n        \"batch_id\": 100000002\n    },\n    {\n        \"status\": \"COMPLETE\",\n        \"results\": [],\n        \"numErrors\": 1,\n        \"errors\": [{\n            \"status\": \"error\",\n            \"category\": \"OBJECT_NOT_FOUND\",\n            \"subCategory\": \"crm.associations.NO_ASSOCIATIONS_FOUND\",\n            \"message\": \"No contact is associated with company 7435569172.\",\n            \"context\": {\n                \"fromObjectId\": [\n                    \"7435569172\"\n                ],\n                \"fromObjectType\": [\n                    \"company\"\n                ],\n                \"toObjectType\": [\n                    \"contact\"\n                ]\n            }\n        }],\n        \"startedAt\": \"2021-12-09T08:57:02.598Z\",\n        \"completedAt\": \"2021-12-09T08:57:02.606Z\",\n        \"uuid\": \"83962368-2b06-46ec-a24e-4b9e53c11119\",\n        \"processed_flag\": 0,\n        \"batch_id\": 100000002\n    },\n    {\n        \"status\": \"COMPLETE\",\n        \"results\": [{\n            \"from\": {\n                \"id\": \"7435757703\"\n            },\n            \"to\": [{\n                \"id\": \"6025351\",\n                \"type\": \"company_to_contact\"\n            }]\n        }],\n        \"startedAt\": \"2021-12-09T08:57:02.607Z\",\n        \"completedAt\": \"2021-12-09T08:57:02.615Z\",\n        \"uuid\": \"7fe57ad2-3433-4ff4-afeb-be8482ff3009\",\n        \"processed_flag\": 0,\n        \"batch_id\": 100000002\n    }\n]\n  }\n}]"
      },
      "name": "Set Example Data",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        460,
        300
      ]
    },
    {
      "parameters": {
        "table": "messi198310",
        "columns": "myjsonfield",
        "additionalFields": {}
      },
      "name": "Postgres1",
      "type": "n8n-nodes-base.postgres",
      "typeVersion": 1,
      "position": [
        900,
        300
      ],
      "credentials": {
        "postgres": {
          "id": "39",
          "name": "ElephantSQL"
        }
      }
    },
    {
      "parameters": {
        "functionCode": "item.myjsonfield = JSON.stringify(item.input);\n\nreturn item;"
      },
      "name": "Stringify JSON",
      "type": "n8n-nodes-base.functionItem",
      "typeVersion": 1,
      "position": [
        680,
        300
      ]
    }
  ],
  "connections": {
    "Start": {
      "main": [
        [
          {
            "node": "Set Example Data",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Set Example Data": {
      "main": [
        [
          {
            "node": "Stringify JSON",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Stringify JSON": {
      "main": [
        [
          {
            "node": "Postgres1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

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:

Also double-checked this using psql:

Hope this works for you :slight_smile:

You my friend are a legend! Works a treat.

Thanks for your help

2 Likes