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 :
- Postgresql INSERT Issue (JSONB) => Works for 1 parameter (not x parameters)
- Non-mustached sql query gets treated as mustached
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]