Issue with Inserting JSON with Multiple Key/Value Pairs in PostgreSQL (jsonb)

Problem

I’m trying to use Method 1 to insert JSON data into my PostgreSQL database via n8n. The challenge arises when attempting to insert more than one key/value pair into the JSON object. With only one key/value pair, the method works fine, but when I try to pass two or more, the query fails, and I can’t seem to find a solution for this issue.

I believe Method 1 is the most logical and clean approach, and I’d prefer to stick with it if possible. I’m looking for guidance on how to resolve the issue and enable the insertion of multiple key/value pairs via this method.

As a workaround, I’ve been able to use Method 2, which does allow me to pass multiple key/value pairs. However, Method 2 has its own challenges, particularly when dealing with quotes ('). I still need a proper way to handle single and double quotes cleanly within the JSON string to avoid syntax issues.

Informations

PostGres : Function

CREATE OR REPLACE FUNCTION insert_ex(p_callback_json jsonb)
RETURNS jsonb AS $$
BEGIN
RETURN p_callback_json;
END;
$$ LANGUAGE plpgsql;

n8n : Query Postgres

SELECT insert_ex(
    $1::jsonb
);

Sources :


Method 1

Method 1

OK

  • 1 Key/Value
{id: "848da11d-e72e-44c5-xxxx-c6fb9f17d366",id2: "848da11d-e72e-44c5-yyyy-c6fb9f17d366"}

Ko

  • 2 Keys/Values
{id: "848da11d-e72e-44c5-xxxx-c6fb9f17d366",id2: "848da11d-e72e-44c5-yyyy-c6fb9f17d366"}

Method 2

Method 2

Inspired by : Non-mustached sql query gets treated as mustached

OK

Ko : with quote

escape test

=> Solution 1 : encode/decode quote ?

=> Solution 2 : double quote

Information on your n8n setup

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

Hey @Valkys , thank you very much for such a detailed report on the issue. It was an awesome job on your side. We really appreciate it.

I have reproduced the issue and am raising it internally as a bug.

Hey @Valkys , the engineering team starts working on the fix. Meanwhile I can share a solution for the method 2.

This should work

SELECT insert_ex(
    '{{ JSON.stringify({id: "848da11d-e72e-44c5-xxxx-c6fb9f17d366",id2: "test of quotestart''quoteend" }) }}'::jsonb
);

Note the escape is done like this '', not the standard way as in \'.