My Adventures in Coding

October 24, 2009

Migrating Data to JSON In SQL Server

Filed under: SQL — Brian @ 10:44 pm
Tags: , ,

Recently I was working on a task that required migrating some existing data (Phone Numbers) from being stored as separate columns in a separate table (e.g., dbo.FriendPhoneNumbers), to being stored as a single JSON document in one column of the table containing all related data (e.g., dbo.Friend).

My task was only to complete a one-time migration, so I did not want to spend a lot of time writing a custom application to complete this work. Just for fun I decided to see if I could first accomplish this migration in SQL. I was also curious if such a SQL statement would be efficient enough to be run in a live production environment.

Using the SQL UPDATE statement below I was able to create 100,000+ JSON documents in just a few seconds.

UPDATE f
SET PhoneNumber = phoneJSON.JSON 
from dbo.Friend f 
join 
(
	select FriendID, REPLACE( 
		'{'+
		CASE WHEN Home IS NOT NULL THEN '"Home":"'+Home+'",' ELSE '' END +
		CASE WHEN Work IS NOT NULL THEN '"Work":"'+Work+'",' ELSE '' END +
		CASE WHEN Fax IS NOT NULL THEN '"Fax":"'+Fax+'",' ELSE '' END +
		CASE WHEN Cell IS NOT NULL THEN '"Cell":"'+Cell+'",' ELSE '' END
		+'}', ',}', '}') as 'JSON'
	from dbo.FriendPhoneNumbers
) as phoneJSON ON phoneJSON.FriendID = f.FriendID
GO

While writing the script I ran into two issues:

  1. Nullable Columns: Wrapped each column in a SQL CASE statement so when a phone number was null, nothing was added to the JSON
  2. Trailing Comma: Added a SQL REPLACE to replace “,}” with “}” to handle the case where the last phone number in the list was null

That was all! It took only a few minutes to write this script.

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: