How can I import a JSON file encoded in UTF-8 in SQL Server 2017, preserving the international characters, avoiding the "mojibake"?

Question

I tried to import json file into sql server using OPENJSON function but it returned with garbled characters. JSON data file encoded with UTF-8. Is there any parameters to use within the SQL query for this issue?

USE CreateTableJSON;
GO
INSERT INTO movies
SELECT movies2.*
 FROM OPENROWSET (BULK 'C:\Users\Mañana\source\repos\WebApplication3\WebApplication3\JSON\Deep Impact.json', SINGLE_CLOB) as j
 CROSS APPLY OPENJSON(BulkColumn)
 WITH( Title nvarchar(MAX), Year int, Rated nvarchar(MAX),
 Released nvarchar(MAX), Runtime nvarchar(100), Genre nvarchar(MAX), 
 Director nvarchar(MAX), Writer nvarchar(MAX), Actors nvarchar(MAX), 
 Plot nvarchar(MAX), Language nvarchar(MAX), Country nvarchar(MAX), 
 Awards nvarchar(MAX), Poster nvarchar(MAX), Ratings nvarchar(MAX), 
 Metascore int, imdbRating float, imdbVotes nvarchar(MAX), imdbID nvarchar(MAX), 
 Type nvarchar(MAX), DVD nvarchar(MAX), BoxOffice nvarchar(MAX), 
 Production nvarchar(MAX), Website nvarchar(MAX), Response nvarchar(MAX)
 ) AS movies2
 GO

I found another related question that was not answered here.

EDIT

Here is a sample JSON:

{
  "Title": "Deep Impact",
  "Year": "1998",
  "Rated": "PG-13",
  "Released": "08 May 1998",
  "Runtime": "120 min",
  "Genre": "Action, Drama, Romance",
  "Director": "Mimi Leder",
  "Writer": "Bruce Joel Rubin, Michael Tolkin",
  "Actors": "Robert Duvall, Téa Leoni, Elijah Wood, Vanessa Redgrave",
  "Plot": "Unless a comet can be destroyed before colliding with Earth, only those allowed into shelters will survive. Which people will survive?",
  "Language": "English",
  "Country": "USA",
  "Awards": "5 wins & 14 nominations.",
  "Poster": "https://m.media-amazon.com/images/M/MV5BYTUwMTY1YmMtN2U5NC00YjkzLTg0YWQtZmEwNTEzZjdkNzQ2XkEyXkFqcGdeQXVyMTQxNzMzNDI@._V1_SX300.jpg",
  "Ratings": [
    {
      "Source": "Internet Movie Database",
      "Value": "6.2/10"
    },
    {
      "Source": "Rotten Tomatoes",
      "Value": "45%"
    },
    {
      "Source": "Metacritic",
      "Value": "40/100"
    }
  ],
  "Metascore": "40",
  "imdbRating": "6.2",
  "imdbVotes": "147,487",
  "imdbID": "tt0120647",
  "Type": "movie",
  "DVD": "15 Dec 1998",
  "BoxOffice": "N/A",
  "Production": "Paramount Pictures",
  "Website": "N/A",
  "Response": "True"
}

The problematic line:

"Actors": "Robert Duvall, Téa Leoni, Elijah Wood, Vanessa Redgrave"

Welcome to Stack Overflow; can you please post a sample of json file you want to import? — Andrea, Nov 11 '18 at 14:28
For what it’s worth, SQL Server 2019 supports UTF-8 natively. — Bink, Jun 11 '19 at 16:28
https://stackoverflow.com/questions/12512687/how-to-use-utf-8-collation-in-sql-server-database — Stu, Jul 09 '19 at 17:42

score 1 · Accepted Answer · answered Jul 13 '19 at 09:16

I solved my problem by first converting the encoding of the JSON file to UTF-16 and then using the following SQL command:

cmd.CommandText =
                    "INSERT INTO movies SELECT temporal.* " +
                    "FROM OPENROWSET (BULK '" + parameter.Value.ToString().Replace("'", "''") +
                    "', SINGLE_NCLOB) as j " +
                    "CROSS APPLY OPENJSON(BulkColumn) " +
                    "WITH( imdbID nvarchar(50), Title nvarchar(MAX), Year int, Rated nvarchar(MAX), " +
                    "Released nvarchar(MAX), Runtime nvarchar(100), Genre nvarchar(MAX), " +
                    "Director nvarchar(MAX), Writer nvarchar(MAX), Actors nvarchar(MAX), " +
                    "Plot nvarchar(MAX), Language nvarchar(MAX), Country nvarchar(MAX), " +
                    "Awards nvarchar(MAX), Poster nvarchar(MAX), " +
                    "Metascore int, imdbRating float, imdbVotes nvarchar(MAX), " +
                    "Type nvarchar(MAX), DVD nvarchar(MAX), BoxOffice nvarchar(MAX), " +
                    "Production nvarchar(MAX), Website nvarchar(MAX), Response nvarchar(MAX)) AS temporal";

How can I import a JSON file encoded in UTF-8 in SQL Server 2017, preserving the international characters, avoiding the "mojibake"?

EDIT

1 Answers1