Tag Archives: Parse

Manually Parsing a JSON String Using JSON.NET

How to manually parse a JSON string using JSON.NET.

Disclaimer

If you jump straight to the references, you will find a very similar set of information, and I strongly encourage people to do so. Additionally, this is probably not the most efficient way to achieve this.

Right, on with the show

Here’s the string that I’ll be parsing, and a little code stolen directly from the link at the bottom to show what it looks like:

static void Main(string[] args)
{
    string json = "{\"documents\":[{\"keyPhrases\":[\"Test new bug\"],\"id\":\"1\"}],\"errors\":[]}";
    JsonTextReader reader = new JsonTextReader(new StringReader(json));
    while (reader.Read())
    {
        if (reader.Value != null)
        {
            Console.WriteLine("Token: {0}, Value: {1}", reader.TokenType, reader.Value);
        }
        else
        {
            Console.WriteLine("Token: {0}", reader.TokenType);
        }
    }
 
    Console.ReadLine();
}

The output for this looks like:

Using this, it’s easier to create a routine to manually parse this. Each object can be tracked by using the Start and EndObject tags. Here’s my unit test to check this works:

[TestMethod]
public void TestJSONParse()
{
    // Arrange
    string json = "{\"documents\":[{\"keyPhrases\":[\"Test new bug\"],\"id\":\"1\"}],\"errors\":[]}";
    // Act
    var result = JsonHelper.ParseResponse(json);
 
    // Assert
    Assert.AreEqual(1, result.Count());
    Assert.AreEqual(1, result.Keys.First());
    string expectedPhrase = result.Values.First().First().ToString();
    Assert.AreEqual("Test new bug", expectedPhrase, false);
}

And here’s the code itself:

/// <summary>
/// Parse the following JSON
/// {"documents":[{"keyPhrases":["Test new bug"],"id":"1"}],"errors":[]}
/// </summary>
/// <param name="response"></param>
/// <returns></returns>
public static Dictionary<int, List<string>> ParseResponse(string response)
{
    Dictionary<int, List<string>> dict = new Dictionary<int, List<string>>();
    object readerValue;
 
    if (!string.IsNullOrWhiteSpace(response))
    {
        JsonTextReader reader = new JsonTextReader(new StringReader(response));
        int? currentValue = null;
        List<string> currentList = null;
 
        while (reader.Read())
        {
            readerValue = reader.Value;
 
            switch (reader.TokenType)
            {
                case JsonToken.PropertyName:                            
                    if (readerValue.ToString() == "id")
                    {
                        reader.Read();
                        currentValue = int.Parse(reader.Value.ToString());                                
                    }
                    else if (readerValue.ToString() == "keyPhrases")
                    {
                        // Do nothing
                    }
                    else if (readerValue.ToString() == "errors")
                    {
                        currentValue = null;
                    }
                    break;
 
                case JsonToken.String:                            
                    currentList.Add(reader.Value.ToString());
                    break;
 
                case JsonToken.StartArray:
                    currentList = new List<string>();
                    break;
 
                case JsonToken.StartObject:
                    currentList = null;
                    currentValue = null;
                    break;
 
                case JsonToken.EndObject:
                    if (currentValue.HasValue)
                    {
                        dict.Add(currentValue.Value, currentList);
                    }
                    break;
            }
        }
    }
 
    return dict;
}

It is messy, and it is error prone, and it would be better done by creating classes and serialising it; however, I’d never attempted to do this manually before, and it’s generally nice to do things the hard way, that way, you can appreciate what you get from these tools.

References

http://www.newtonsoft.com/json/help/html/ReadJsonWithJsonTextReader.htm

SQL Server xp_sprintf and xp_sscanf

I learned about these two functions accidentally last year, and I wanted to try them both out to see what they do. A long time ago, I used to be a C programmer, so the concepts of the two functions are familiar to me (assuming they are similar to their C cousins).

xp_sprintf

This is kind of like the C function sprintf… except it isn’t. Firstly, it only supports the %s placeholder. This means that you can’t represent, or format a float, like you can in C. Secondly, it looks like it only works with strings of 255 characters or less. Here’s an example:

DECLARE @myString varchar (255)
EXEC xp_sprintf @myString OUTPUT, 
    'There are %s fields in the table %s', '3', 'TestTable'

SELECT @myString

sql1

Admittedly, that seems a bit pointless; but what if we do this:


DECLARE @colCount INT
DECLARE @colCountStr varchar(255)
DECLARE @myString varchar(255)
DECLARE @tableName varchar(255) 

set @tableName = 'Audit'

SELECT @colCount = COUNT(COLUMN_NAME) 
FROM INFORMATION_SCHEMA.COLUMNS WHERE 
TABLE_CATALOG = 'TestDB' AND TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = @tableName

SELECT @colCountStr = CONVERT(varchar, @colCount)

EXEC xp_sprintf @myString OUTPUT, 
    'There are %s fields in the table %s', @colCountStr, @tableName

SELECT @myString

sql2

It makes a bit more sense. However, there are a number of restrictions with the procedure. There is the 255 characters, you can only substitute strings and, because it’s a procedure, you can’t include it in a query.

A better way: FORMATMESSSAGE

FORMATMESSAGE is a function, but it supports all the standard placeholders:


DECLARE @colCount INT
DECLARE @tableName varchar(255) 

set @tableName = 'Audit'

SELECT FORMATMESSAGE('There are %d fields in the table %s', COUNT(COLUMN_NAME), @tableName) 
FROM INFORMATION_SCHEMA.COLUMNS WHERE 
TABLE_CATALOG = 'TestDB' AND TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = @tableName

sql2

xp_sscanf

This looks like a far more useful function; as it allows parsing of a string. Whether or not you believe you should be storing your data in a manner that requires parsing, one day, you will need to do it. Consider this table:

scanf

So, I want to analyse this data; how about:

select *
from dbo.[Audit]

DECLARE @colour varchar(255)
DECLARE @text varchar(255)

SELECT @text = AuditText from dbo.[Audit]

EXEC xp_sscanf @text, 'The sky is %s today', @colour OUTPUT

select @colour

sql3

The eagle eyed amongst you might notice a slight issue here – because it’s a procedure, it can’t be used inside a query. It’s also hampered by the same restrictions of 255 characters, etc… I couldn’t find an xp_scanf equivalent of FORMATMESSAGE, so I rolled my own:

CREATE FUNCTION ScanFWrapper 
(	
	@inputText varchar(255),
	@formatText varchar(255)
)
RETURNS varchar(255)
AS
BEGIN	
	DECLARE @Result varchar(255)

	EXEC xp_sscanf @inputText, @formatText, @Result OUTPUT
		
	RETURN @Result

END
GO

Admittedly, it’s not very generic, but you can call it like this:

SELECT dbo.ScanFWrapper(AuditText, 'The sky is %s today') 
FROM dbo.[Audit]

sql4

Conclusion

There are a number of xp_* methods, and they seem to be one-off procedures, so I’m probably being unfair on them in trying to compare them to their C equivalents.