Postgres Integration: json query parsing leads to broken queries

[sdsys-ch]

Checklist

• [X] I have searched budibase discussions and github issues to check if my issue already exists

Hosting

• Self
  • Method: docker compose
  • Budibase Version: 2.28.4

Describe the bug Adding a new SQL query for a Postgres DB (haven't checked internal or others) results in Query Error: syntax error at or near "\" if the query contains a json constructor (pot. only related to COALESCE statements, but don't think s - untested).

Looking at the logfiles, the query contains \n or '\t' chars that haven't been stripped by the sql preprocessing code apparently:

bbpostgres  | 	SELECT
bbpostgres  | 	    i.*,
bbpostgres  | 	    COALESCE(json_build_object('contact', ic.contact_info),'{"contact": {}}'::json) AS contact_details,\n    COALESCE(json_build_object('name', in.name_info),'{"name": {}}'::json) AS name_details

Removing the line breaks in the create query window makes the query succeed.

To Reproduce Steps to reproduce the behavior:

Create table and table data in your DB:

CREATE TABLE individuals (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    phone VARCHAR(20),
    birth_date DATE,
    nationality VARCHAR(50)
);

-- sample data
INSERT INTO individuals (first_name, last_name, email, phone, birth_date, nationality) VALUES
('Jane', 'Austen', '[email protected]', '1775-1817', '1775-12-16', 'English'),
('Ernest', 'Hemingway', '[email protected]', NULL, '1899-07-21', 'American'),
('Virginia', 'Woolf', NULL, '1882-1941', '1882-01-25', 'British'),
('Franz', 'Kafka', NULL, NULL, '1883-07-03', 'Czech'),
('Gabriel', 'García Márquez', '[email protected]', '1927-2014', '1927-03-06', 'Colombian'),
('Agatha', 'Christie', '[email protected]', '1890-1976', '1890-09-15', 'British'),
('Leo', 'Tolstoy', NULL, '1828-1910', '1828-09-09', 'Russian'),
('Chimamanda', 'Ngozi Adichie', '[email protected]', NULL, '1977-09-15', 'Nigerian');

• Go to Datasource -> Queries -> Create New Query adding name etc.
• Add this as the query:

WITH individual_contact AS (
    SELECT 
        id,
        json_build_object(
            'email', email,
            'phone', phone
        ) AS contact_info
    FROM 
        individuals
),
individual_name AS (
    SELECT 
        id,
        json_build_object(
            'first_name', first_name,
            'last_name', last_name
        ) AS name_info
    FROM 
        individuals
)
SELECT 
    i.*,
    COALESCE(json_build_object('contact', ic.contact_info),'{"contact": {}}'::json) AS contact_details,
    COALESCE(json_build_object('name', iname.name_info),'{"name": {}}'::json) AS name_details
FROM 
    individuals i
LEFT JOIN 
    individual_contact ic ON i.id = ic.id
LEFT JOIN 
    individual_name iname ON i.id = iname.id
WHERE 
    i.id = 1;

• Run the query. query errors
• You can then remove the line break between the COALESCE statements: query works.

Desktop:

{
  "browser": {
    "language": "en-GB",
    "userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/128.0.0.0 Safari/537.36",
    "platform": "MacIntel",
    "vendor": "Google Inc."
  },
  "server": {
    "budibaseVersion": "2.28.4",
    "hosting": "docker-compose",
    "nodeVersion": "v20.14.0",
    "platform": "linux",
    "cpuArch": "x64",
    "cpuCores": 2,
    "cpuInfo": "Intel(R) Xeon(R) Silver 4110 CPU @ 2.10GHz",
    "totalMemory": "3.7916183471679688GB",
    "uptime": "226 day(s), 20 hour(s), 30 minute(s)"
  }
}C

[linear[bot]]

BUDI-8629 (Postgres?) SQL query with json and coalesce statements breaks sql parsing because of newline in-between statements

[sdsys-ch]

After having a quick look at the postgres integration code, this seems to be the culprit:

const JSON_REGEX = /'{.*}'::json/s

The dotall //s will match newlines which is wanted, but might eat all the fish.

const JSON_REGEX = /'{\s*.*?\s*}'::json/gs would fix this?

[sdsys-ch]

Fixed in 2.32.15. Thanks!