SQLServerCentral Article

A Function to Split JSON Data

,

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:

idnamevalue
1Apples20000
2Oranges400
3Apricots507

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:

sequenceconverted to
\bbackspace
\fformfeed
\nnewline
\rcarriage return
\ttab

 

For more details about the formatting of a JSON string visit their website http://www.json.org

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

ColumndatatypeDescription
idintThe order the items were parsed in.
namenvarchar(255)

In a object, the name portion.

In an array, this will be NULL.

value

nvarchar(max)(SS2K8)

nvarchar(4000)(SS2K)

In an object, the value portion. In an array the data itself.
offsetintThe offset in the input string that the element was found. 1 is the starting position.
lengthintThe length of the string processed.
colonintIn an object, the location of the colon character.
nestedintIf nested array or object was detected.
errcntintNumber of errors found.
msgvarchar(8000)Messages containing errors or warnings about the record.

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.

namedatatypedefaultDescription
dateStyleint121The style number used by the T_SQL CONVERT function. 121 =yyyy-mm-dd hh:mi:ss.mmm(24h). This is used when decoding \/DATE(n)\/.
idStartint1The starting value for the id column.
idStepint1The integer value to add to the last value inserted into the id column.
debugbit0If 1 then the option settings are inserted into the returned table. The id values are negative offset from the idStart value.
removeQuotesbit1If removeQuotes =1 and surrounding double quotes are detected, then the surrounding double quotes are removed and the control pattern \" is replaced with a ".
splitCharnchar(1),(comma)The JSON format uses a comma for the character to split on. The splitChar option allows you to specify a different character to split on.
verboseint1

Sets the level of messages displayed.

0 = no messages

1 = error messages

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)
idnamevalue
1Apples20000
2Oranges400
3Apricots507

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)
idnamevalue
1NULLApples:20000
2NULLOranges:400
3NULLApricots:507

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

idnamevaluenestederrcntmsg
1hex convertHi!00 
2date convert2008-11-25 02:00:00.00000 
3bad date value\/DATE(1227578400)\/011 messages. 1) "\/DATE(" found at position1 but the date is not 13 numeric characters.
4quotes removed"Good Morning!", he said.00 
5nested 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] }30 

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

idnamevaluenestederrcntmsg
1Width80000 
2Height60000 
3TitleJSON split function00 
4Thumbnail{"Url": "http://www.example.com/image/SS2008jsontn", "Height": 50, "Width": "95" }10 
5IDs[1, 33, 77]10 

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:;}')
value
Apples
Oranges
Apricots
Grapes

 

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

namevalue
remove quote test"Good Morning!", he said.
" remove quote test ""\"Good Morning!\", he said."

 

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

idvalue
10Apples
13Oranges
16Apricots
19Grapes

 

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:

idnamevaluemsg
-9versionSS2008 V1.0 Aug 2009Option Debug
-8authorRic Vander ArkOption Debug
-7licenseMicrosoft Public License (Ms-PL)Option Debug
-6verbose1Option Debug
-5splitChar,Option Debug
-4removeQuotes1Option Debug
-3idStep1Option Debug
-2idStart1Option Debug
-1debug1Option Debug
0dateStyle100Option Debug

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:

namevalue
default2008-11-25 02:00:00.000
100Nov 25 2008 2:00AM
10111/25/2008
1022008.11.25
10325/11/2008
109Nov 25 2008 2:00:00.000AM
1272008-11-25T02:00:00

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.

nameSQL 2000 value (S2K)SQL 2008 value (S2K8)
02008-11-25 02:00:00.0002008-11-25 02:00:00.000
12008-11-25 02:00:00.0002008-11-25 02:00:00.001
22008-11-25 02:00:00.0032008-11-25 02:00:00.002
32008-11-25 02:00:00.0032008-11-25 02:00:00.003
42008-11-25 02:00:00.0032008-11-25 02:00:00.004
52008-11-25 02:00:00.0072008-11-25 02:00:00.005
62008-11-25 02:00:00.0072008-11-25 02:00:00.006
72008-11-25 02:00:00.0072008-11-25 02:00:00.007
82008-11-25 02:00:00.0072008-11-25 02:00:00.008
92008-11-25 02:00:00.0102008-11-25 02:00:00.009
102008-11-25 02:00:00.0102008-11-25 02:00:00.010
112008-11-25 02:00:00.0102008-11-25 02:00:00.011
122008-11-25 02:00:00.0132008-11-25 02:00:00.012
132008-11-25 02:00:00.0132008-11-25 02:00:00.013
142008-11-25 02:00:00.0132008-11-25 02:00:00.014
152008-11-25 02:00:00.0172008-11-25 02:00:00.015
162008-11-25 02:00:00.0172008-11-25 02:00:00.016
172008-11-25 02:00:00.0172008-11-25 02:00:00.017
182008-11-25 02:00:00.0172008-11-25 02:00:00.018
192008-11-25 02:00:00.0202008-11-25 02:00:00.019

 

Resources

Rate

4.76 (34)

You rated this post out of 5. Change rating

Share

Share

Rate

4.76 (34)

You rated this post out of 5. Change rating