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

[hirokib]

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

[vitaly-t]

Why would you expect 'null' returned? It returns the correct null as string, the same way that any JSON formatter does, including JSON.stringify.

pgp.as.format('$1, $1:json, $2, $2:json, $2:raw', [null, {value: null}]);
//=> null, null, '{"value":null}', '{"value":null}', {"value":null}

If you want to get 'null', then pass it in as a string 'null'-value, not as just null-value.

[hirokib]

Wow didn't expect such a quick response.

My expectation is that if I'm setting the value of a json key:value pair, that a JSON representation of null would be used (i.e. a stringed 'null' when passing it into the update query as opposed to a postgres null which makes the whole column null.

What's happening now is that update statement will essentially drop the entire json document rather than set the key within the json to null

[hirokib]

If I'm super specific about the code it looks like:

query = npm.formatting.formatQuery(query, values);

where query:
UPDATE "Claim" SET "raw" = jsonb_set("raw", '{$2:raw}', $3:json, TRUE) WHERE "id" = $1

and values:
Array(3) ["claimIdValue", "userClaimDetails", null]

I expect that the returned query is actually set to
UPDATE "Claim" SET "raw" = jsonb_set("raw", '{userClaimDetails}', 'null', TRUE) WHERE "id" = 'claimIdValue'

and not:
UPDATE "Claim" SET "raw" = jsonb_set("raw", '{userClaimDetails}', null, TRUE) WHERE "id" = 'claimIdValue'

because the latter results in my table column raw being null instead of raw->>userClaimDetails being null.

Am I at least correct with my expectations of what setting null as a JSON value means semantically or is the expectation that a null value coerced to JSON should actually result in a postgres null?

[hirokib]

I think maybe the confusion here is that, yes, technically the ::json is returning null as a string. The issue is that the query itself is a string so when postgres sees this, it's not interpreting this null value as a JSON null per se because a JSON null in postgres when passed in is actually 'null' and a stringified null would be '"null"' <-- needs the double quotes to make it a string null instead of a JSON null

[vitaly-t]

You are passing the value not as JSON, but as an open value null, so it is formatted correctly.

[hirokib]

I'm not sure I follow.

The docs say "When a variable name ends with :json, explicit JSON formatting is applied to the value."

and JSON.stringify(null) <-- an open value null is 'null' not null.

If I were to have passed a javascript object {prop1: null}, the formatting would've properly converted it to '{"prop1": null}'. Why should an open null be different?

[vitaly-t]

The docs say "When a variable name ends with :json, explicit JSON formatting is applied to the value."

Yes, and JSON presentation for value null is string null, it is not quoted 'null'.

and JSON.stringify(null) <-- an open value null is 'null' not null.

that is incorrect; do your tests.

[vitaly-t]

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.

[hirokib]

Ya I recognize that this example was actually completely incorrect so sorry about that.

I'm not looking for a debugging session which this may sound like but I genuinely don't feel like the semantics here are correct. I also understand that you're the expert here so my statements are meant to make things clear and not to imply you don't understand.

Let's say I'm not using pg-promise for a second and I'm just using some SQL query editor.

If I wanted to set the value of a key:value pair of a jsonb column in postgres, where the key exists and it's JSON value is null I would write a query like

UPDATE "MyTable" SET "jsonColumn" = jsonb_set("jsonColumn", '{jsonKey}', 'null', TRUE)

The reason that null here is in single quotes is because because JSON values in postgres must be wrapped in single quotes. A string is '"thestring"'. Likewise native json types like numbers and null are '2' and 'null'.

My point is that, when I pass an open null value to pg-promise using the :json filter, I expect that the native json values to be wrapped in a single quote. I'm not sure why passing something like 2 or {prop1: null} works correctly with the result being
'2' or '{"prop1":null}' but null doesn't get that single quote.

[vitaly-t]

The formatting engine here is generic, it doesn't understand the specific you want conveyed in your example. You need to provide it yourself. See my previous reply.

[vitaly-t]

The bottom line is, if you do not agree with how something is formatted, use Custom Type Formatting.

[hirokib]

Sure. Thanks for the workaround. I'll use the Custom Type Formatting.

I still think from a user semantics perspective, which I think is where we disagree, if the pg-promise pre-processed query looks like:
UPDATE "MyTable" SET "jsonColumn" = jsonb_set("jsonColumn", '{$2:raw}', $3:json, TRUE)

Where the value 2 results in this:
UPDATE "MyTable" SET "jsonColumn" = jsonb_set("jsonColumn", '{jsonKey}', '2', TRUE)

and the value {prop1: null} results in this:
UPDATE "MyTable" SET "jsonColumn" = jsonb_set("jsonColumn", '{jsonKey}', '{"prop1": null}', TRUE)

then null should result in
UPDATE "MyTable" SET "jsonColumn" = jsonb_set("jsonColumn", '{jsonKey}', 'null', TRUE)

and not
UPDATE "MyTable" SET "jsonColumn" = jsonb_set("jsonColumn", '{jsonKey}', null, TRUE)

[hirokib]

I'm closing the issue. Thank you for your help and for hearing me out. I appreciate the discussion and will implement your suggestions.

[hirokib]

Okay definitely going to seem like an asshole now but if you pass in 'null' as a value in your array of values, you actually turn it into '"null"' so how am I supposed to retain both the :json filter and also pass in null where I get the JSON null?

CTFs are pretty heavy handed just to get a null escaped to JSON null when passing in a simple object no? I don't have a common obj structure I'm passing into the update query.

If it's still CTF, then I'll close again 😿

[hirokib]

Unrelated but your avatar image is amazing.

[vitaly-t]

'"null"'

Just remove :json filter from $3:json, use $1 and pass 'null' into it.

[hirokib]

Are you suggesting I just run JSON.stringify on the object prior then in the application code instead of using :json? Most of the time it's an object.

[vitaly-t]

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.

[hirokib]

Ya I mean it seems like something pretty basic so I can't imagine it's an issue with the library but I also can't understand what I'm missing here. I'm also not super experienced with JSON in postgres so alas I'm not sure what expected behavior outside of what I read is.

I mean I could technically write the query to use a NULL coalescing function like
UPDATE "MyTable" SET "jsonColumn" = jsonb_set("jsonColumn", '{$2:raw}', coalesce($3:json, 'null'), TRUE) to handle the very specific case of a top level null but I felt like I was not using the library right if that was the case. In literally every other use case, pg-promise has worked exactly as expected semantically (not literally. I mean just it expected the way I would expect).

This may be what others are doing as well. I have no insight however to that.

[hirokib]

My, perhaps incorrect feeling is
json(data, raw) { data = resolveFunc(data); if (isNull(data)) { throwIfRaw(raw); returnnull; <--- should actually be'null'because this is only ever called for a top level null and not null as a value in a KV pair } return $to.json(data, raw); },

I think the other question I wonder is, should something with a json filter ever return a postgres null? Ignoring how the actual formatting works, semantically if I'm passing null to a :json filter, I wouldn't expect it to ever equal a postgres null because that has a completely different meaning.

I understand that a JSON null is null, but in the context of a postgres query, without quotes, null to postgres is a postgres null and not a json null.

Anyway, going to see how the coalesce operator feels and let you know.

[vitaly-t]

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

[hirokib]

Okay 😛 well I think the actual best suggestion for any rando who asks about this again is to just use the null coalescing operator in postgres for this specific case:

    "MyTable"
SET 
    "jsonColumn" = jsonb_set("jsonColumn", '{jsonKey}', coalesce($1:json, 'null')::jsonb, TRUE)

This results in a javascript null being properly set to a JSON null when passed in through a :json filter rather than a postgres null (if that's the desired effect)

This works as expected with

    "jsonColumn"#>'{jsonKey}'
FROM
    "MyTable"

returning for
strings: "string values"
numbers: 23
null: null

Thanks for your time.

[vitaly-t]

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.

[hirokib]

Ah, I totally forgot that I can use the pgp.as.json to format outside of sql. Already deployed the changes using coalesce so I'll probably just keep that in for now but thanks!