Columns in SQL Server containing JSON show their values with unicode escape sequences

Completed

Hello,

Using MS SQL Server, I have tables with VARCHAR(MAX) columns where I am storing JSON.

In Datagrip, whether I'm inside or outside the Value Editor, and regardless of having defined or not the Highlighting Language for that column to JSON, I'm seeing single-quotes as escaped unicode sequences:



Changing the field to NVARCHAR(MAX) doesn't produce a different output. Here's the generated DDL from within DataGrip:

create table dbo.EventStore
(
    Id            uniqueidentifier default newid() not null
                  constraint PK_EventStore primary key,
    Timestamp     datetime2(6)                     not null,
    CorrelationId uniqueidentifier                 not null,
    EventType     nvarchar(max)                    not null collate Latin1_General_100_CI_AS_SC_UTF8,
    EventSource   nvarchar(max)                    not null collate Latin1_General_100_CI_AS_SC_UTF8,
  Payload       varchar(max)                     not null collate Latin1_General_100_CI_AS_SC_UTF8
)
0
1 comment

Nevermind. Problem resolved.

The problem was how data was being saved to the database, not how it was being displayed. I'm using C# with JsonSerializer and forgot all about its block lists, which include the single-quote character.

The only way to avoid JsonSerializer escaping single-quotes is to use `UnsafeRelaxedJsonEncoding`.

`var options = new JsonSerializerOptions { Encoder = JavaScriptEncoder.UnsafeRelaxedJsonEscaping };`



1

Please sign in to leave a comment.