Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

A Function to Split JSON Data

By Ric Vander Ark,

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:

id name value
1 Apples 20000
2 Oranges 400
3 Apricots 507

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:

sequence converted to
\b backspace
\f formfeed
\n newline
\r carriage return
\t tab

 

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

Column datatype Description
id int The order the items were parsed in.
name nvarchar(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.
offset int The offset in the input string that the element was found. 1 is the starting position.
length int The length of the string processed.
colon int In an object, the location of the colon character.
nested int If nested array or object was detected.
errcnt int Number of errors found.
msg varchar(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.

name datatype default Description
dateStyle int 121 The 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)\/.
idStart int 1 The starting value for the id column.
idStep int 1 The integer value to add to the last value inserted into the id column.
debug bit 0 If 1 then the option settings are inserted into the returned table. The id values are negative offset from the idStart value.
removeQuotes bit 1 If removeQuotes =1 and surrounding double quotes are detected, then the surrounding double quotes are removed and the control pattern \" is replaced with a ".
splitChar nchar(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.
verbose int 1

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)
id name value
1 Apples 20000
2 Oranges 400
3 Apricots 507

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)
id name value
1 NULL Apples:20000
2 NULL Oranges:400
3 NULL Apricots: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

id name value nested errcnt msg
1 hex convert Hi! 0 0  
2 date convert 2008-11-25 02:00:00.000 0 0  
3 bad date value \/DATE(1227578400)\/ 0 1 1 messages. 1) "\/DATE(" found at position1 but the date is not 13 numeric characters.
4 quotes removed "Good Morning!", he said. 0 0  
5 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] } 3 0  

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

id name value nested errcnt msg
1 Width 800 0 0  
2 Height 600 0 0  
3 Title JSON split function 0 0  
4 Thumbnail {"Url": "http://www.example.com/image/SS2008jsontn", "Height": 50, "Width": "95" } 1 0  
5 IDs [1, 33, 77] 1 0  

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

name value
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

id value
10 Apples
13 Oranges
16 Apricots
19 Grapes

 

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:

id name value msg
-9 version SS2008 V1.0 Aug 2009 Option Debug
-8 author Ric Vander Ark Option Debug
-7 license Microsoft Public License (Ms-PL) Option Debug
-6 verbose 1 Option Debug
-5 splitChar , Option Debug
-4 removeQuotes 1 Option Debug
-3 idStep 1 Option Debug
-2 idStart 1 Option Debug
-1 debug 1 Option Debug
0 dateStyle 100 Option 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:

name value
default 2008-11-25 02:00:00.000
100 Nov 25 2008 2:00AM
101 11/25/2008
102 2008.11.25
103 25/11/2008
109 Nov 25 2008 2:00:00.000AM
127 2008-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.

name SQL 2000 value (S2K) SQL 2008 value (S2K8)
0 2008-11-25 02:00:00.000 2008-11-25 02:00:00.000
1 2008-11-25 02:00:00.000 2008-11-25 02:00:00.001
2 2008-11-25 02:00:00.003 2008-11-25 02:00:00.002
3 2008-11-25 02:00:00.003 2008-11-25 02:00:00.003
4 2008-11-25 02:00:00.003 2008-11-25 02:00:00.004
5 2008-11-25 02:00:00.007 2008-11-25 02:00:00.005
6 2008-11-25 02:00:00.007 2008-11-25 02:00:00.006
7 2008-11-25 02:00:00.007 2008-11-25 02:00:00.007
8 2008-11-25 02:00:00.007 2008-11-25 02:00:00.008
9 2008-11-25 02:00:00.010 2008-11-25 02:00:00.009
10 2008-11-25 02:00:00.010 2008-11-25 02:00:00.010
11 2008-11-25 02:00:00.010 2008-11-25 02:00:00.011
12 2008-11-25 02:00:00.013 2008-11-25 02:00:00.012
13 2008-11-25 02:00:00.013 2008-11-25 02:00:00.013
14 2008-11-25 02:00:00.013 2008-11-25 02:00:00.014
15 2008-11-25 02:00:00.017 2008-11-25 02:00:00.015
16 2008-11-25 02:00:00.017 2008-11-25 02:00:00.016
17 2008-11-25 02:00:00.017 2008-11-25 02:00:00.017
18 2008-11-25 02:00:00.017 2008-11-25 02:00:00.018
19 2008-11-25 02:00:00.020 2008-11-25 02:00:00.019

 

Resources:

fnSplitJson2V1.0S2K8.sql | fnSplitJson2V1.0S2K.sql
Total article views: 10796 | Views in the last 30 days: 103
 
Related Articles
FORUM

display only "All" if "Select All" option is checked

display only "All" if "Select All" option is checked

FORUM

Function with Select * that references a View

Select * in the Function does not reference new columns added to a View

FORUM

"function"

"function"

FORUM

Select from Variable Table Name under Function.

Fail to select an input table name under a Function !

FORUM

Multi Value parameter does not persist in the textbox when "Select All" option in selected for the param

Multi value parameter clears off when "Select All" option is selected for the report

Tags
javascript    
json    
split    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones