fix(Postgres Node): Account for JSON expressions

[dana-gill]

Summary

Fixes the bug where query params which include multiple JSON key/vals fails in Postgres Node

Workflows for testing:

{
  "nodes": [
    {
      "parameters": {
        "operation": "executeQuery",
        "query": "SELECT insert_ex(\n    $1::jsonb\n);",
        "options": {
          "queryReplacement": "={{ JSON.stringify(\n\n{id: \"848da11d-e72e-44c5-xxxx-c6fb9f17d366\",id2: \"848da11d-e72e-44c5-yyyy-c6fb9f17d366\"}\n\n) }}"
        }
      },
      "id": "4c1d023e-ab9c-467f-a43f-a830b214eefa",
      "name": "This is the problem1",
      "type": "n8n-nodes-base.postgres",
      "typeVersion": 2.5,
      "position": [
        1560,
        300
      ],
      "credentials": {
        "postgres": {
          "id": "UeO14x83grJWzgYI",
          "name": "Postgres account 2"
        }
      }
    },
    {
      "parameters": {
        "operation": "executeQuery",
        "query": "SELECT insert_ex(\n        $1::jsonb\n\n);",
        "options": {
          "queryReplacement": "={{ JSON.stringify(\n\n{id: $$66$$}\n\n) }}"
        }
      },
      "id": "110341e5-7b89-4a49-a4bd-fe860a6fa7fd",
      "name": "This works1",
      "type": "n8n-nodes-base.postgres",
      "typeVersion": 2.5,
      "position": [
        1560,
        120
      ],
      "credentials": {
        "postgres": {
          "id": "UeO14x83grJWzgYI",
          "name": "Postgres account 2"
        }
      }
    }
  ],
  "connections": {},
  "pinData": {}
}

Related Linear tickets, Github issues, and Community forum posts

https://linear.app/n8n/issue/NODE-1753/postgres-node-inserting-json-with-multiple-keyvalue-via-query
https://community.n8n.io/t/issue-with-inserting-json-with-multiple-key-value-pairs-in-postgresql-jsonb/53939/

Review / Merge checklist

• PR title and summary are descriptive. (conventions)
• Docs updated or follow-up ticket created.
• Tests included.
• PR Labeled with release/backport (if the PR is an urgent fix that needs to be backported)

[codecov]

Codecov Report

Attention: Patch coverage is 85.00000% with 3 lines in your changes missing coverage. Please review.

Files with missing lines	Patch %	Lines
...gres/v2/actions/database/executeQuery.operation.ts	80.00%	1 Missing and 1 partial ⚠️
...ages/nodes-base/nodes/Postgres/v2/helpers/utils.ts	90.00%	1 Missing ⚠️

📢 Thoughts on this report? Let us know!

[dana-gill]

fwiw, i'm fairly certain the codecov is complaining because I converted the loop into a map and therefore touched lines which are yet to be tested. however, code coverage is increased by 0.21% 😄

[ShireenMissi]

LGTM 🎉

[github-actions]

⚠️ Some Cypress E2E specs are failing, please fix them before merging

[cypress]

n8n    Run #8434

Run Properties:   Failed #8434  •  c6702ececb: 🌳 🖥️ browsers:node18.12.0-chrome107 🤖 dana-gill 🗃️ e2e/*

Project	n8n
Branch Review	node-1753-inserting-json-multiple-key-val-fails
Run status	Failed #8434
Run duration	04m 22s
Commit	c6702ececb: 🌳 🖥️ browsers:node18.12.0-chrome107 🤖 dana-gill 🗃️ e2e/*
Committer	Dana Lee
View all properties for this run ↗︎

---

Test results
Failures	1
Flaky	0
Pending	0
Skipped	0
Passing	387
View all changes introduced in this branch ↗︎

---

Tests for review

  14-mapping.cy.ts • 1 failed test

View Output Video

Test		Artifacts
Data mapping > maps expressions from json view		Test Replay Screenshots Video

  24-ndv-paired-item.cy.ts • 0 failed tests

View Output

Test		Artifacts

  27-two-factor-authentication.cy.ts • 0 failed tests

View Output

Test		Artifacts

  17-workflow-tags.cy.ts • 0 failed tests

View Output

Test		Artifacts

  28-debug.cy.ts • 0 failed tests

View Output

Test		Artifacts

The first 5 failed specs are shown, see all 35 specs in Cypress Cloud.

[github-actions]

⚠️ Some Cypress E2E specs are failing, please fix them before merging

[github-actions]

⚠️ Some Cypress E2E specs are failing, please fix them before merging

[github-actions]

⚠️ Some Cypress E2E specs are failing, please fix them before merging

[github-actions]

✅ All Cypress E2E specs passed

[janober]

Got released with n8n@1.74.1

[janober]

Got released with n8n@1.74.1

[janober]

Got released with n8n@1.74.0

[janober]

Got released with n8n@1.74.0