Empty arrays don't round trip as JSON correctly

[ungrim97]

When storing an array to a JSON type column (either json or jsonb), an empty [] is returned as {} on the SELECT

This appears to be because arrays are parsed to PostgreSQL array literals (https://github.com/brianc/node-postgres/blob/392a7f4a66d111cc4e9fd14253f09215441eed98/packages/pg/lib/utils.js#L16). This works for most JSON data, except when you provide an empty JS array ([]), which then is converted to the PS literall '{}' which means that the return is a Javascript {} not a [] as you input

Stringifying the data before calling insert does fix the issue as a work around but was unexpected.

CREATE TABLE test (field json);

pg.query(`INSERT INTO test (field) VALUES ($1)`, [[]]);
pg.query(`SELECT field FROM test`).then((results) => console.log(results.rows[0].field)); // Should be [] but is actually {}

[charmander]

It’s not the parsing; it’s a serialization ambiguity. You need to serialize arrays as JSON manually when they’re top-level:

pg.query(`INSERT INTO test (field) VALUES ($1)`, [JSON.stringify([])]);

If the array weren’t empty during this insertion, it’d produce an error.

pg might be able to improve on this (producing an error even for empty arrays) by specifying type codes in the Parse message. Something to consider for the next major version.

[ungrim97]

So is the issue that Pg can't know how to handle the array (i,.e it can't do the JSONification itself)?

I am fine with having the JSONify the field first, but it would have been nice if there was consistancy.

If there is a warning about this in the Docs I couldn't find it either. Any idea where you think I should add it and I can probably do a PR for that