pg-promise: JSON formatting does not properly wrap null values

Expected behavior

$1::json where null is passed in should return 'null'

Actual behavior

Returns null

Steps to reproduce

CREATE TABLE ( “id” VARCHAR, “raw” jsonb <-- already has documents that contain the key “jsonbKey” )

QueryFile with query: UPDATE "Table" SET "raw" = jsonb_set("raw", '{$2:raw}', $3:json, TRUE) WHERE "id" = $1 “raw” is a jsonb column

Values [“randomValue”, “jsonbKey”, null]

Expected Return value of formatting.js array(query, array, raw, options): UPDATE "Table" SET "raw" = jsonb_set("raw", '{jsonbKey}', 'null', TRUE) WHERE "id" = 'randomValue'

Received: UPDATE "Table" SET "raw" = jsonb_set("raw", '{jsonbKey}', null, TRUE) WHERE "id" = 'randomValue'

Environment

Version of pg-promise: 10.5
OS type (Linux/Windows/Mac): Mac
Version of Node.js: 12.18

About this issue

Original URL
State: closed
Created 4 years ago
Comments: 24 (10 by maintainers)

Most upvoted comments

I think it would work to simply wrap the value like this:

const jsonData = a => a === null ? 'null' : pgp.as.json(a);

And then pass in data as jsonData(data), without using any formatting filters or SQL-conversion.

vitaly-t on Aug 27, 2020

At this point, I’ll leave it for you to investigate on your own 😄 I’m sure you will figure it out.

vitaly-t on Aug 26, 2020

No, I thought you were trying to just pass in an open value. If you want to pass in anything including an object, you would need proper JSON formatting, and according to your previous posts, you are not happy with the default JSON formatting, which means that JSON.stringify won’t help you either.

There is something fundamentally wrong with your issue here, I just can’t quite put my finger on it, because I barely ever use JSON in my databases. But a lot of other developers do, and no-one complained yet. That includes MassiveJS that was built on top of pg-promise, and is an edvanced JSON-processing engine.

vitaly-t on Aug 26, 2020

As I stated in the beginning, you can just pass in 'null' as the value, and get the expected result.

Alternatively, if you want to use a JSON-null, you can create one, using Custom Type Formatting:

const jsonNull = {toPostgres: () => 'null'};

and then use specifically as JSON-null formatting value.

vitaly-t on Aug 26, 2020