Skip to content

Remove transformation of JSON_VALUE() to OPENJSON() for varbinary when using RETURNING #36474

@roji

Description

@roji

In #33440, we added a transform for accessing a binary value within JSON. the standard encoding of binary within JSON is base64 - and that's how we serialize; but in query, we generate a JSON_VALUE() wrapped by a CAST to the type we want to get. However, CAST to varbinary in SQL Server does not do base64 decoding, but rather SQL Server's special varbinary string encoding format. To work around this, we transformed JSON_VALUE() over varbinary to OPENSJON(), which does do base64 decoding.

SQL Server 2025 is bringing support for the JSON_VALUE() RETURNING clause, which will also do base64 decoding for varbinary; once that works, the transformation to OPENJSON is no longer necessary (note that RETURNING can only be used with the new json data type, so the transformation is still required when the user maps JSON to nvarchar(max) - "legacy mode").

Confirmed that this will not be happening in time for the October release, so 10 should be planned without it.

Metadata

Metadata

Assignees

Projects

No projects

Relationships

None yet

Development

No branches or pull requests

Issue actions