Managing JSON in SQL Server Scripts

Have you ever used Microsoft’s SQL Server or Azure SQL Database to store JSON data? I hadn’t heard of this pattern until last year, and I was honestly surprised to discover that Microsoft actively facilitates it. But since then, I’ve been using it a lot, and it definitely has its advantages. (You can read more about the concept on Microsoft Docs.) But there is one distinct disadvantage I’ve encountered, and it comes into play when writing SQL scripts.

At rest, JSON data in SQL Server is stored as a string (typically an NVARCHAR(MAX)). Microsoft provides some helpful methods for manipulating these giant strings piece-by-piece, but in some cases I have to work with the whole thing at once, such as in seed scripts or other times when I need to insert or replace an entire document. SQL Server does support multiline strings, so you can technically put a fully formatted JSON document straight into a SQL script, but there are drawbacks to that approach. Editors will generally not see it as anything other than a string, so you won’t get proper JSON syntax highlighting, auto completion, or linting. Depending on the size of your JSON document, it might also burn up a lot of your screen real estate and make it harder to navigate the file.

For these reasons, I’ve gotten into the habit of using a separate editor to work with these mega-strings as actual JSON files, and then minifying the results to put in the actual SQL file. This works well enough, but it still gets tedious if my JSON ends up with a lot of apostrophes or single quotes in it. Those have to be escaped for SQL Server, but if I do that in my source JSON, then it’s not as accurate for documentation or other uses I might have for it besides stuffing it in a SQL field. So I have to manually fix all those characters every time I paste in the JSON.

To solve this problem, I decided to make myself a little command-line utility to assist with preparing JSON data for pasting into my SQL scripts. I call it json4tsql.

This is an extremely simple Node.js tool that just takes some input you give it, either from a file or from stdin, and does the following:

1. Parse it as JSON (or, crash if it’s not JSON until I add error handling).
2. Minify it to remove indentation and other whitespace.
3. Escape any single quote characters.
4. Enclose the whole string in a pair of single quote characters.

It’s nothing very fancy, but it gets the job done and saves me the hassle of manually escaping apostrophes when sticking some JSON into a SQL statement.

Microsoft Docs has more sophisticated ways to get JSON into SQL Server, including loading files directly using special methods. But I hope json4tsql can benefit some more people like me who work with simpler setups and just want to shave off some of the manual effort.