JSON stands for JavaScript Object Notation, and is a lightweight data-interchange format. The fnSplitJSON2 function splits a JSON string and returns the information in a table. The returned table has extra columns which indicate if there is a nested JSON string or if an error was detected.

This is an example of using the fnSplitJson2 function:

 -- object example, braces surround the name:value objects
SELECT id, name, value FROM dbo.fnSplitJson2('{Apples:20000,Oranges:400,Apricots:507}',NULL)

Results:

I believe you will find the JSON format easy to use and very readable, and the fnSplitJson2 function a very useful tool.

I use this function to

• use one parameter instead of many parameters in stored procedures or functions,
• pass a data string into stored procedures from a website using AJAX calls,
• allow for dynamic setting of T-SQL variables in stored procedures and functions,
• general splitting of strings.

In this article I will give a brief introduction to the JSON format, describe the fnSplitJson2 syntax and options, and show some examples of its use.

Introduction to the JSON format

JSON has two string format structures, an array and an object. An object is an unordered set of name/value pairs. An array is an ordered collection of values.

An object begins with { (left brace) and ends with } (right brace). Each name is followed by colon and the name/value pairs are separated by a comma. For example:

{Apples:20000, Oranges:400, Apricots:507}

An array begins with [ (left bracket) and ends with ] (right bracket). Values are separated by a comma. For example:

[Apples,Oranges,Apricots]

JSON supports the use of hex characters in the format of \u0000 where each 0 represents a hex digit. The function will convert them to the nchar value. Other characters sequences that are automatically converted are:

Function Description

Syntax

dbo.fnSplitJson2(@InputString, @Options)

Input Parameters (Arguments)

@InputString nvarchar(max) (SS2K8) nvarchar(4000) (SS2K)

@Options nvarchar(1023)= NULL

Notes

There are 2 versions of the function available, one which is compatible with SQL Server 2000 and 2005, and the other for SQL Server 2008. I will use the following terms when referring to these:

• SS2K SQL Server 2000 (2005) version
• SS2K8 SQL Server 2008 version

Return Table

The output table gives more data than you will normally use, but I have found it helpful in debugging input strings and in determining if there are nested objects or arrays.

Options

The @Option parameter is used to change default settings when the function is run. They are passed into the function using the JSON object format.

Using the Function

JSON Object String

In this example the function splits an JSON object formatted string into 3 records, using the name and value columns.

 -- object example, braces surround the name:value objects
 SELECT id, name, value FROM dbo.fnSplitJson2('{Apples:20000,Oranges:400,Apricots:507}',NULL)

JSON Array String

In this example the function splits the JSON array formatted string into 3 records. Note that the only difference between this input string and the input string from the previous example is the surrounding brackets instead of the surrounding braces. The name column will always be NULL for a JSON array.

 -- array example, brackets surround the comma seperated array
 SELECT id, name, value FROM dbo.fnSplitJson2('[Apples:20000,Oranges:400,Apricots:507]',NULL)

General Example

The following JSON object string has 5 named/value. There is an error in one, and nested JSON objects in another. Match each result line with it corresponding name/value pair.

SELECT id, name, value, nested, errcnt, msg FROM dbo.fnSplitJson2(
 '{
 hex convert:\u0048\u0069\u0021
 ,date convert:\/DATE(1227578400000)\/
 ,bad date value:\/DATE(1227578400)\/
 ,"quotes removed":"\"Good Morning!\", he said."
 ,nested 3: { "Width":  800,
    "Height": 600,
    "Title":  "JSON split function",
    "Thumbnail": {"Url": "http://www.example.com/image/SS2008jsontn",
                         "Height": 50,
                         "Width":  "95"
                 },   
    "IDs": [1, 33, 77]
      }
 }'
 , NULL)

Results

With record 5 (name = nested 3), you would take the value and run it into the function:

SELECT id, name, value, nested, errcnt, msg  FROM dbo.fnSplitJson2(
 '{ "Width":  800,      "Height": 600,      "Title":  "JSON split function",      "Thumbnail": {"Url": "http://www.example.com/image/SS2008jsontn",                           "Height": 50,                           "Width":  "95"                    },          "IDs": [1, 33, 77]   }'
 , NULL)

Results

Using the Options

Split Character

Normally the comma is the split character. In this example the semicolon is set to be the split character.

SELECT value FROM dbo.fnSplitJson2('[Apples;Oranges;Apricots;Grapes]', '{splitChar:;}')

removeQuotes

This example illustrates the use of the

Script

SELECT name, value FROM dbo.fnSplitJson2('{"remove quote test ":"\"Good Morning!\", he said."}', NULL)
 UNION ALL
 SELECT name, value FROM dbo.fnSplitJson2('{"remove quote test":"\"Good Morning!\", he said."}', '{removeQuotes:0}')

Results

idStart and idStep Settings

In this example the start value for the id column is set to 10 and each following id value increments by 3.

SELECT id, value FROM dbo.fnSplitJson2('[Apples,Oranges,Apricots,Grapes]', '{idStart:10,idStep:3}')

Results

Dynamic setting of T-SQL variables

For this example I will use the options setting code used within the fnSplitJson2 function. As you can see from the options available for fnSplitJson2, if I had to write it with a parameter for each option the code would look something like:

 CREATE FUNCTION dbo.fnFoo
 (@InputString nvarchar(max)
 , @dateStyle int = 121
 , @idStart int = 1
 , @idStep int = 1
 , @debug bit = 0
 ,@removeQuotes bit = 1
, @splitChar nchar(1) = NULL
 , @verbose int = 1
 )
...
 -- set default values if null passed in
 SELECT @dateStyle = ISNULL(@dateStyle, 121)
 , @idStart = ISNULL(@idStart, 1)
 , @idStep = ISNULL(@idStep, 1)
 , @debug = ISNULL(@debug, 0)
 ,@removeQuotes = ISNULL(@removeQuotes, 1)
, @splitChar = ISNULL(@splitChar, ',')
 , @verbose = ISNULL(@verbose, 1)
 ...

where parameters 2 -8 are the options. In the function body we handle validation and set the default if a parameter has a NULL value.

To call this function we would have to specify all 7 option parameters, which makes the function difficult to call (in my opinion).

DECLARE @dateStyle int = 121
 , @idStart int = 1
 , @idStep int = 1
 , @debug bit = 0
 ,@removeQuotes bit = 1
, @splitCharnchar(1) = NULL
 , @verbose int = NULL
 SELECT * FROM dbo.fnFoo('[hi]', @dateStyle, @idStart, @idStep, @debug, @removeQuotes, @splitChar, @verbose)

Using the fnSplitJson2 function allows us to have one parameter for the options instead of seven. In the body if the code we declare the variables and set the default values. fnSplitJson2 then calls itself with the @Options as the @InputString. It then validates and sets the variables using the returned table. For example:

CREATE FUNCTION dbo.fnSplitJson2
 (@InputString nvarchar(max)
 , @Options nvarchar(1023)= NULL
 )
...
 DECLARE @dateStyle int = 121
 , @idStart int = 1
 , @idStep int = 1
 , @debug bit = 0
 ,@removeQuotes bit = 1
 , @verbose int = 1
...
 IF(@Options IS NOT NULL)
 BEGIN
        SELECT @verbose = CASE WHEN name = 'verbose' ANDISNUMERIC(value)= 1 THEN value ELSE @verbose END
        , @RemoveQuotes = CASE WHEN name = 'removeQuotes' AND ISNUMERIC(value)= 1 THEN value ELSE @RemoveQuotes END
        , @idStart = CASE WHEN name = 'idStart' ANDISNUMERIC(value)= 1 THEN value ELSE @idStart END
        , @idStep = CASE WHEN name = 'idStep' ANDISNUMERIC(value)= 1 THEN value ELSE @idStep END
        , @dateStyle = CASE WHEN name = 'dateStyle' ANDISNUMERIC(value)= 1 THEN value ELSE @dateStyle END
        , @Debug = CASE WHEN name = 'debug' ANDISNUMERIC(value)= 1 THEN value ELSE @Debug END
        , @splitChar = CASE WHEN name = 'SplitChar' THEN value ELSE @splitChar END
        FROM dbo.fnSplitJson2(@Options,NULL);
 END
...

The function call is now simpler, with only two required parameters. To use the default settings the call is:

SELECT * FROM dbo.fnSplitJson2('[hi]',NULL)

To set the options dateStyle and idStart the call is:

SELECT * FROM dbo.fnFoo('[hi]','{dateStyle:100,idStart:0}')

Notice that we only need to just pass in the options we want to set (2 in the example above), not all of them.

The function has the capability to return the option variable's values by setting the debug option equal to 1. The next example displays the debug and change the dateStyle to 100 (from the default 121):

Script

-- return option settings
 SELECT id, name, value, msg FROM dbo.fnSplitJson2(NULL, '{debug:1,dateStyle:100}')

Results:

Handling Dates

There is no date literal in JSON, however one popular way to include a date value in is to encode the date in the format of "\/DATE(1227578400000)\/", where the number is the number of milliseconds since 1970-01-01 UTC. The function converts this format into a T-SQL date style format. The default style is 121. The dateStyle option is used to change the default style.

Script:

SELECT name, value FROM dbo.fnSplitJson2('{default:\/DATE(1227578400000)\/}', NULL)
 UNION ALL
 SELECT name, value FROM dbo.fnSplitJson2('{100:\/DATE(1227578400000)\/}', '{dateStyle:100}')
 UNION ALL
 SELECT name, value FROM dbo.fnSplitJson2('{101:\/DATE(1227578400000)\/}', '{dateStyle:101}')
 UNION ALL
 SELECT name, value FROM dbo.fnSplitJson2('{102:\/DATE(1227578400000)\/}', '{dateStyle:102}')
 UNION ALL
 SELECT name, value FROM dbo.fnSplitJson2('{103:\/DATE(1227578400000)\/}', '{dateStyle:103}')
 UNION ALL
 SELECT name, value FROM dbo.fnSplitJson2('{109:\/DATE(1227578400000)\/}', '{dateStyle:109}')
 UNION ALL
 SELECT name, value FROM dbo.fnSplitJson2('{127:\/DATE(1227578400000)\/}', '{dateStyle:127}')

Results:

Reference:

"SQL Server Books Online", "CAST and CONVERT", "Date and Time Styles" for date style information.

http://msdn.microsoft.com/en-us/library/bb299886.aspx

http://weblogs.asp.net/bleroy/archive/2008/01/18/dates-and-json.aspx

Date Conversion Error example:

Due to the limitation with the rounding of milliseconds before SQL Server 2008, the S2K version of fnSplitJson2 will give rounding errors. I ran the following script for each version.

Script:

DECLARE @json varchar(8000)
 SELECT @json =
 + '{000:\/DATE(1227578400000)\/'
 + ',001:\/DATE(1227578400001)\/'
 + ',002:\/DATE(1227578400002)\/'
 + ',003:\/DATE(1227578400003)\/'
 + ',004:\/DATE(1227578400004)\/'
 + ',005:\/DATE(1227578400005)\/'
 + ',006:\/DATE(1227578400006)\/'
 + ',007:\/DATE(1227578400007)\/'
 + ',008:\/DATE(1227578400008)\/'
 + ',009:\/DATE(1227578400009)\/'
 + ',010:\/DATE(1227578400010)\/'
 + ',011:\/DATE(1227578400011)\/'
 + ',012:\/DATE(1227578400012)\/'
 + ',013:\/DATE(1227578400013)\/'
 + ',014:\/DATE(1227578400014)\/'
 + ',015:\/DATE(1227578400015)\/'
 + ',016:\/DATE(1227578400016)\/'
 + ',017:\/DATE(1227578400017)\/'
 + ',018:\/DATE(1227578400018)\/'
 + ',019:\/DATE(1227578400019)\/'
 + '}'
 SELECT * FROM dbo.fnSplitJson2(@json, NULL)

Results

The columns from both versions are combined. Notice that the values in the SQL 2000 value column have rounded the milliseconds.