PostgreSQL Node - Issue with JSONB[] Data Type Handling

[Burak_Bayraktaroglu]

Hey n8n community,

I’m encountering an issue with the PostgreSQL node when trying to insert or update data into a jsonb[] column. Here’s the situation:

What I’m Trying to Achieve:

• I have a column named assets in my PostgreSQL table, which is of type jsonb[].
• I want to insert or update this column with an array of JSON objects directly from my workflow without manually writing SQL commands.

Current Behavior:

• When I attempt to set the assets column value directly as an array of objects (using expressions like {{ $json.assets }}), I get the following error:

Error;
  Invalid input for 'assets' [item 0]
  'assets' expects a array but we got '::jsonb[]'

• I’ve tried to explicitly cast the value to jsonb[] within the node’s interface by using {{ $json.assets }}::jsonb[], but this approach results in the node interpreting ::jsonb[] as part of the data rather than a SQL cast.

Expected Behavior:

• The node should recognize the incoming data as an array of JSON objects and automatically handle the conversion to PostgreSQL’s jsonb[] type without needing manual SQL casting in the node interface.

Steps to Reproduce:

1. Set up a workflow where assets is an array of objects.
2. Use a PostgreSQL node for Insert or Update operation.
3. Map the assets field from your data to the assets column in the table, using expressions if necessary.
4. Attempt to execute the workflow.

Additional Information:

• n8n version: 1.72.1
• Database: postgresdb
• n8n EXECUTIONS_PROCESS setting: own, main
• Running n8n via: Docker
• Operating system: Linux
• PostgreSQL version: 16.1

Example of data structure for assets:

[
  {
    "assetKey": "exampleKey",
    "campaignId": "exampleId",
    "punchCount": 0,
    "assignDate": "2024-12-17T19:29:45.976Z",
    "redeemed": false
  }
]

Has anyone else encountered this issue? Any suggestions for handling this without resorting to custom SQL queries directly in the node? Or is there a known workaround?

Thanks for any help or insights!

Example workflow

[n8n]

It looks like your topic is missing some important information. Could you provide the following if applicable.

• n8n version:
• Database (default: SQLite):
• n8n EXECUTIONS_PROCESS setting (default: own, main):
• Running n8n via (Docker, npm, n8n cloud, desktop app):
• Operating system:

[Burak_Bayraktaroglu]

I already had some info but turned them into required format, as ** *. (I hope)

[ihortom]

Hey @Burak_Bayraktaroglu , I created the test table as

CREATE TABLE assets2(
  id int
  , assets JSONB []
  , PRIMARY KEY (id)
);

Then inserted the assets[] without any issue.