Passing an array as query parameter in Postgresql

[honzapav]

Hello, I have a follow-up question related to a topic that has been discussed but is closed: Postgres use array as a query parameters

I want to pass an array into a Postgresql query as a query parameter. Ideally, I would create a query like this:

SELECT array_agg(id) AS ids
FROM business_case
WHERE status = 'done' 
  AND product_id = ANY($1)

And pass an array like this: [1,2,3,4] (it comes from previous node like $json.id_array. But this doesn’t work.

I know I can convert it with .join() and use the IN operator, but I want to understand how n8n works (and also wanted to use the ANY operator.

I found a solution:

SELECT array_agg(id) AS ids
FROM business_case
WHERE status = 'done' 
  AND product_id = ANY(string_to_array($1, ',')::int[])

with query parameter: {{ $json.id_array.join() }}.

Why my original query doesn’t work?
Thank you very much!
Honza

[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:

[ihortom]

@honzapav , for ANY($1) to work $1 should be in the form of '{1,2,3}' and not '1,2,3'. That is, curly braces as opposed to square braces or no braces.

When you use ANY(string_to_array($1, ',')::int[]) with {{ $json.id_array.join() }} you esentially submit the query in the form ANY(string_to_array('1,2,3', ',')::int[]) and the conversion to the Postgres array takes place in Postgres itself rather than n8n.