Sql Server OPENJSON returns unicode string

Question

I have issue with SQL Server and JSON :/

There are a lot of JSON strings coming from a remote database (which I only have read permission). The problem is that all of them are parsed into unicodes and I don't know how to turn them into UTF-8 !

Take a look at this JSON for example:

{
   "0":{
      "id":1,
      "name":"\\u062a\\u0646"
   },
   "1":{
      "id":2,
      "name":"\\u0628\\u0633\\u062a\\u0647 10\\u062a\\u0627\\u06cc\\u06cc"
   },
   "2":{
      "id":3,
      "name":"\\u0639\\u062f\\u062f 10\\u062a\\u0627\\u06cc\\u06cc"
   },
   "5":{
      "id":6,
      "name":"\\u0639\\u062f\\u062f 1000\\u062a\\u0627\\u06cc\\u06cc"
   },
   "6":{
      "id":7,
      "name":"\\u06a9\\u06cc\\u0644\\u0648\\u06af\\u0631\\u0645"
   }
}

As you can see field name is parsed into unicode. I did this to extract data into a table:

SELECT Units.*
FROM OPENJSON(@Json) AS i
CROSS APPLY OPENJSON(i.[value]) WITH (
   [Id] INT '$.id',
   [Name] NVARCHAR(MAX) '$.name'
) AS Units

It will then show something like this as result:

• Id: 1, Name: \u062a\u0646
• Id: 2, Name: \u0628\u0633\u062a\u0647 10\u062a\u0627\u06cc\u06cc
• ...

But I need something like this as result:

• Id: 1, Name: تن
• Id: 2, Name: عدد 10تایی
• ...

Answer 1

Some explanations from ECMA-404 standard: A JSON string is a sequence of Unicode code points wrapped with quotation marks (U+0022). ... If the code point is in the Basic Multilingual Plane (U+0000 through U+FFFF), then it may be represented as a six-character sequence: a reverse solidus, followed by the lowercase letter u, followed by four hexadecimal digits that encode the code point.

It seems, that the names are escaped, so a possible option is to replace the escaped \:

JSON:

DECLARE @json nvarchar(max) = N'{
   "0":{
      "id":1,
      "name":"\\u062a\\u0646"
   },
   "1":{
      "id":2,
      "name":"\\u0628\\u0633\\u062a\\u0647 10\\u062a\\u0627\\u06cc\\u06cc"
   },
   "2":{
      "id":3,
      "name":"\\u0639\\u062f\\u062f 10\\u062a\\u0627\\u06cc\\u06cc"
   },
   "5":{
      "id":6,
      "name":"\\u0639\\u062f\\u062f 1000\\u062a\\u0627\\u06cc\\u06cc"
   },
   "6":{
      "id":7,
      "name":"\\u06a9\\u06cc\\u0644\\u0648\\u06af\\u0631\\u0645"
   }
}'

Statement:

SELECT Units.*
FROM OPENJSON(REPLACE(@Json, N'\\u', N'\u')) AS i
CROSS APPLY OPENJSON(i.[value]) WITH (
   [Id] INT '$.id',
   [Name] NVARCHAR(MAX) '$.name'
) AS Units

Result:

Id  Name
1   تن
2   بسته 10تایی
3   عدد 10تایی
6   عدد 1000تایی
7   کیلوگرم