I have been using different tools and techniques to output valid JSON from SQL Server Databases, specially from imported Open Data datasets in CSV format and data that I have scraped from different web sites. Most of the time it’s somewhat a painful and tedious process, switching back and forth from these tools, copying and pasting… to finally come up with something decent!

So I decided to write something fast, lightweight, and useful that I could use directly from SQL Server Management Studio using T-SQL code and have outputted validated JSON.

In a JSON file a value can be a string in double quotes, or a number, or true or false or null, or an object or an array, and these structures can be nested. (the script below does not handle nested structures)

json

For more detailed information on the JSON specification -> json.org

An example; let’s start by creating a simple table named ‘Game’ and insert some data (looks familiar!).

Now from this populated table and using the following script below SQLToJSON.sql let’s try to output some valid JSON.
Two important variables; @TableViewName and @Columns.
On line 17 we assign the value ‘Game’ to the @TableNameView and on line 20 a comma separated list of attributes ‘GameID, GameDateTime, Country, City, Stadium, [Group], Team01, Team02, Result’ to the @Columns variable.

Execute the above query and the output generates the following valid JSON.

Observations:

  • Numerical values (int, decimal, float, money…) and in Exact or Approximate data type categories are not double quoted.
  • Character strings (char, varchar, nchar, nvarchar) are double quoted [script does not support text data type]
  • Datetime data type values are double quoted – more on that in following section
  • Nulls are not double quoted. It is not a value, per se: it is a value outside the domain of the variable and indicates missing or unknown data.
  • The exceptions…

    Now for date, datetime2, and time data types to render correctly you need to create a view to convert (cast) and assign these values accordingly.

    Did not want to deal with the the famous error message: Argument data type time is invalid for argument 1 of isdate function.
    As for the bit data type and anything that needs to to be represented with true or false I used the case statement…

    An example; Create the following view named ‘vw_GameJSON’

    Change the values for the following variables @TableViewName to ‘vw_GameJSON’ and ‘GameID, GameDate, GameTime, Country, City, Stadium, [Group], Team01, Team02, IsActive’ for @Columns.

    Make the following changes in the script;

    Execute the query and look at the generated output

    Notice the GameDate and GameTime attributes are now outputted as string and the IsActive value with true.

    I have since turned this script into a stored procedure that I named ‘sp_SQLToJSON’ and placed in the master database – I can now call this proc from any database and voilà!

    You can download the script here or from GitHub

    Tagged with →