Non-mustached sql query gets treated as mustached

[EricSGS]

Describe the issue/error/question

I’ve got a workflow that takes in some orders and each order has line items on it (in an array under SalesOrderDetails). I’m trying to upsert to my postgres sales_order_line_item table. The upsert works fine, but I also want to delete any rows that aren’t in the upsert.

At first I tried splitting the line items into separate items in n8n, but when I do that it just seems to delete everything that isn’t the most recent line item.

Then I tried leaving each sales order as an item and passing an array of SalesOrderDetails and I can’t get that to work with n8n at all, both when in the query and in queryparams.

What is the error message (if any)?

No error message if I do it as seperate items, just deletes everything but the most recent. If do it as a single item then I get object Object errors because it isn’t letting me pass in an array.

Please share the workflow

This is if I’m running it as separate items:

If I’m trying to pass it in as an array I try something like:

BEGIN;
WITH data_to_insert AS (
    SELECT
        (SELECT id FROM sales_order WHERE number = '{{ $json["SalesOrderNo"] }}') AS sales_order_id,
        elems ->> 'SalesOrderRowQuantity' AS quantity,
        elems ->> 'SalesOrderRowProductSKU' AS sku,
        elems ->> 'SalesOrderRowDetailID' AS megaventory_id,
        'GSO-201625' AS sales_order_number
    FROM jsonb_array_elements('[{{ $json["SalesOrderDetails"] }}]'::jsonb) AS elems
),...

Note I’ve tried it with and without manually adding the [] around $json[“SalesOrderDetails”]. Although $json[“SalesOrderDetails”] is an array, when passing it into a node it always passes in as a series of objects {}, {}, {} is then failed to be understood.

Information on your n8n setup

• n8n version: 0.233.1
• Database you’re using (default: SQLite):
• Running n8n with the execution process [own(default), main]:
• Running n8n via [Docker]:

[barn4k]

hi @EricSGS

I’m not familiar well with postgres, but I suppose you can’t use the jsonb_array_elements method as it requires an array of elements. But {{ $json["SalesOrderDetails"] }} will return an Object of objects.

If you need an array, then you need to replace it with {{ Object.entries($json["SalesOrderDetails"]) }}

[EricSGS]

That didn’t work, but it did set me down the right path.

It should be jsonb_array_elements(‘{{ JSON.stringify($json.SalesOrderDetails) }}’::jsonb)

I have to turn it into a string, then wrap the string in singlequotes, then turn it back into an array (and then turn all of the integers back into integers using CAST())

Seems a big overly complex, but it works.