How to deal with escaped characters especially " " in nested jsonb arrays for PostgresSQL Update?

Questions
1

Describe the problem/error/question

Trying to upsert an JSONB array of such with " ":[{"id":291035,"name":"Dana \"Scott\" Simmons","avatar":""}]

What is the error message (if any)?

malformed array literal: “{”{"id":291035,"name":"Dana \“Scott\” Simmons","avatar":""}“}”

Unexpected array element.

Please share your workflow

Share the output returned by the last node

Information on your n8n setup

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

The error comes from PostgreSQL, not from n8n directly. The cause is how the string is interpreted as an array of JSON elements, not as valid JSON itself.

In PostgreSQL, when passing a jsonb array, a valid JSON string is not enough. It must conform to the syntax of a PostgreSQL array.

In this case, the escaped quotes " inside the name field are causing a conflict by interpreting the string as an array literal, not a jsonb.

Instead of using a jsonb array, insert a JSON string and then convert it in PostgreSQL:
SELECT jsonb_array_elements(‘[{“id”:291035,“name”:“Dana "Scott" Simmons”,“avatar”:“”}]’::jsonb)

If you really need to use a jsonb, you must escape everything:
‘{“{"id":291035,"name":"Dana "Scott" Simmons","avatar":""}”}’::jsonb

3

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.