Home > Uncategorized > #Parse #JSON in MS #SQL server @procurios

#Parse #JSON in MS #SQL server @procurios

aaeaaqaaaaaaaacvaaaajdg4mwqwmtbjltayyjitndaxmy1imtq2lwu1ztazmte4nzm0yw

If your application stores data in SQL server as a JSON value, you will find it difficult to read out individual properties on this data. This means that you can’t do joins on fields that are held within the data, or any aggregate queries. It’s just not flexible at all.

So, as the name suggests, I’ve used a C# CLR UDF (User defined function) to do this, where it takes in the string, processes it within the CLR and returns it to SQL server.

To give you a few “anti-patterns” of things that don’t work. You may find that SQL server only supports a limited set of .NET assemblies, so you can’t import Newtonsoft to handle the JSON, nor can you use System.Runtime.Serialization, which is not allowed by SQL server either. So I had to use a home grown JSON parser, by Procurios (http://techblog.procurios.nl/k/news/view/14605/14863/how-do-i-write-my-own-parser-(for-json).html) – thanks @procurios

So, creating a new CLR Project in Visual Studio, I added the procurios JSON class, and this code;

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString CLR_ReadJSON(string json, string property)
{
var o = JSON.JsonDecode(json) as Hashtable;
return o[property].ToString();
}

I compiled the DLL, transferred it to the server, and added a new Assembly, which I’ve called CLR_DataTools (The above code was in a namespace called StoredProcedures)

Then I wrote the following SQL code to define the UDF

CREATE FUNCTION [dbo].[CLR_UdfReadJson]
(
@json [nvarchar](4000),
@property [nvarchar](4000)
)
RETURNS nvarchar(max)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [CLR_DataTools].[StoredProcedures].[CLR_ReadJSON]
GO

And that’s all you need! (Although this took me a few hours to figure out)

As an aside, here’s some code I wrote to handle XPath queries on XML within SQL server, but I am aware that there are better ways to do this;

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString CLR_ReadXML(string xml, string xPath)
{
XmlDocument xdoc = new XmlDocument();
xdoc.LoadXml(xml);
var xn = xdoc.DocumentElement.SelectSingleNode(xPath);
var strXml = xn.InnerXml;
return strXml;
}

and then defined the SQL UDF as follows;

CREATE FUNCTION [dbo].[CLR_UdfReadXML]
(
@xml [nvarchar](4000),
@xPath [nvarchar](4000)
)
RETURNS nvarchar(max)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [CLR_DataTools].[StoredProcedures].[CLR_ReadXML]
GO

 

Advertisements
Categories: Uncategorized
  1. December 6, 2016 at 6:00 pm

    I do realise that this approach can only read the top level elements of a JSON element, which was all I needed, feel free to expand on this, and paste your improvements as a comment. Thanks!

  1. No trackbacks yet.

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

%d bloggers like this: