Importing Json File to SQL Server into a variable

A short article today of how to import JSON file to SQL Server into a variable, which can then
be used to insert it into a column of type NVARCHAR(MAX) of a table. The maximum size of NVARCHAR(MAX) is 2 Gb, so you can
import large Json files using this datatype. If the Json is small and below 4000 chars, use for example NVARCHAR(4000) instead. Here is a SQL script to import the json file using OPENROWSET and Bulk import. We also pass in the path to the folder where the json file is. It is put in the same folder as the .sql file script. Note that the variable $(FullScriptDir) is passed in via a .bat file (shown further below) and we expect the .json file to be in the same folder as the .bat file. You can provide a full path to a .json file instead and skip the .bat file here and import a json file, but it is nice to load the .json file from the same folder as the .sql file in case you want to copy the .sql and .json file to another server and not having to provide and possibly having to adjust the full path. Sql-script import_json_file_openrowset.sql:

DECLARE @JSONFILE VARCHAR(MAX); 

SELECT @JSONFILE = BulkColumn
FROM OPENROWSET (BULK '$(FullScriptDir)\top-posts.json', SINGLE_CLOB) AS j;

PRINT 'JsonFile contents: ' + @JSONFILE

IF (ISJSON(@JSONFILE)=1) PRINT 'It is valid Json';

The .bat file here passes the current folder as a variable to the sql script runsqlscript.bat

@set FullScriptDir=%CD%
sqlcmd -S .\SQLEXPRESS  -i import_json_file_openrowset.sql

This outputs:

sqlcmd -S .\SQLEXPRESS  -i import_json_file_openrowset.sql
JsonFile contents: [
   {
      "Id":6107,
      "Score":176,
      "ViewCount":155988,
      "Title":"What are deconvolutional layers?",
      "OwnerUserId":8820
   },
   {
      "Id":155,
      "Score":164,
      "ViewCount":25822,
      "Title":"Publicly Available Datasets",
      "OwnerUserId":227
   }
]
It is valid Json

With the variable JSONFILE you can do whatever with it such as inserting it to a column in a new row of a table for example.
Importing json from a string directly using OPENJSON

It is also possible to directly just import the JSON from a string variable like this:

DECLARE @JSONSTRINGSAMPLE VARCHAR(MAX) 

SET @JSONSTRINGSAMPLE = N'[
 {
    "Id": 2334,
    "Score": 4.3,
    "Title": "Python - Used as scientific tool for graphing"
 },
{
    "Id": 2335,
    "Score": 5.2,
    "Title": "C# : Math and physics programming"
 }
]';

SELECT * FROM OPENJSON (@JSONSTRINGSAMPLE) WITH (
    Id INT,
    Score REAL,
    Title NVARCHAR(100)
)

Functional programming - Fork combinator in C# to combine results from parts

This article will discuss a wellknown combinator called Fork which allows you to combine the mapped result. Consider the following extension methods to fork on an object. Fork here means to operate on parts of the object such as
different properties and apply functions on these parts and then recombine the results into a combined result via a specified combinator function, sometimes called a 'join function'.

public static class FunctionalExtensions {

	public static TOutput Map<TInput, TOutput>(
		this TInput @this,
		Func<TInput, TOutput> func) => func(@this);

	public static TOutput Fork<TInput, TMiddle, TOutput>(
		this TInput @this,
		Func<IEnumerable<TMiddle>, TOutput> combineFunc,
		params Func<TInput, TMiddle>[] parts)
	{
		var intermediateResults = parts.Select(p => p(@this));
		var result = combineFunc(intermediateResults);
		return result;
    }

	public static TOutput Fork<TInput, TMiddle, TOutput>(
		this TInput @this,
		Func<TInput, TMiddle> leftFunc,
		Func<TInput, TMiddle> rightFunc,
		Func<TMiddle, TMiddle, TOutput> combineFunc)
	{
		var leftResult = leftFunc(@this); // @this.Map(leftFunc);
		var rightResult = rightFunc(@this); // @this.Map(rightFunc);
		var combineResult = combineFunc(leftResult, rightResult);
		return combineResult;
	}

}

Let's take a familiar mathematical example, calculating the Hypotenuse in a triangle using Pythagorean theorem. This states that the length of the longest side A of a 'right triangle' is the square root of the sum of the squares of the shorter sides B and C : A = √(B² + C²) Consider this class:

  
  
  public class Triangle {
	public double CathetusA { get; set; }
	public double CathetusB { get; set; }	
	public double Hypotenuse { get; set; }
  }
  
    

Let's test the first Fork helper extension method accepting two functions for specifying the left and right components:

  
  
  	var triangle = new Triangle
	{
		CathetusA = 3,
		CathetusB = 4
	};
	
	triangle.Hypotenuse = triangle.Fork(	
		t => t.CathetusA * t.CathetusA, 
		t => t.CathetusB * t.CathetusB, 
		(l,r) => Math.Sqrt(l+r));
		
	Console.WriteLine(triangle.Hypotenuse);
  
  
  

This yields '5' as the answer via the forked result above. A simple example, but this allows us to create a simple combinatory logic example on an object of any type using functional programming (FP). Let's look at a simpler example just combining multiple properties of an object with a simple string-join, but using the Fork version supporting arbitrary number of parts / components:

 


public class Person {
	public string JobTitle { get; set; }
	public string FirstName { get; set; }
	public IEnumerable<string> MiddleNames { get; set; }
	public string LastName { get; set; }
}

var person = new Person{
		JobTitle = "Detective",
		FirstName = "Alexander",
		MiddleNames = new[] { "James", "Axel" },
		LastName = "Foley"
	};
	
string contactCardText = person.Fork(parts => string.Join(" ", parts), p => p.FirstName,
 p => string.Join(" ", p.MiddleNames), p => p.LastName);
Console.WriteLine(contactCardText);

This yields: Alexander James Axel Foley Fork can be very useful in many cases you need to 'branch off' on an object and recombine parts of the object with some specific function, either two parts or multiple parts and either continue to work on the results or retrieve the results.

Functional programming - the Tee function to inspect current state in a chained expression

In this article we will look at helper extension methods of StringBuilder first to better support chaining StringBuilder. We will work on the same StringBuilder instance and add support for appending lines or character to the StringBuilder given a condition. Also example showing how to aggregate lines from a sequence is shown and appending formatted lines. Since C# interpolation has become more easy to use, I would suggest you keep using AppendLine instead. Here is the helper methods in the extension class :

public static class StringBuilderExtensions {

	public static StringBuilder AppendSequence<T>(this StringBuilder @this, IEnumerable<T> sequence, Func<StringBuilder, T, StringBuilder> fn)
	{
		var sb = sequence.Aggregate(@this, fn);
		return sb;
	}
	
	public static StringBuilder AppendWhen(this StringBuilder @this, Func<bool> condition, Func<StringBuilder, StringBuilder> fn) => 
		condition() ? fn(@this) : @this;
		
    public static StringBuilder AppendFormattedLine(
		this StringBuilder @this,
		string format,
		params object[] args) => 
			@this.AppendFormat(format, args).AppendLine();
	
}

Now consider this example usage:

void Main()
{
	var countries = new Dictionary<int, string>{
		{ 1, "Norway" },
		{ 2, "France" },
		{ 3, "Austria" },
		{ 4, "Sweden" },
		{ 5, "Finland" },
		{ 6, "Netherlands" }
	};
	string options = BuildSelectBox(countries, "countriesSelect", true);
	options.Dump("Countries"); //dump is a method available in Linqpad to output objects 
	
}

private static string BuildSelectBox(IDictionary<int, string> options, string id, bool includeUnknown) =>
		new StringBuilder()
			.AppendFormattedLine($"<select id=\"{id}\" name=\"{id}\">")
			.AppendWhen(() => includeUnknown, sb => sb.AppendLine("\t<option value=\"0\">Unknown</option>"))
			.AppendSequence(options, (sb, item) => sb.AppendFormattedLine("\t<option value=\"{0}\">{1}</option>", item.Key, item.Value))
			.AppendLine($"</select>").ToString();   

What if we wanted to inspect the state of the stringbuilder in the middle of these chained expression. Is it possible to output state in such lengthy chained functional expressions? Yes, that is called the Tee method inside functional programming patterns. Other might call it for Tap such as used in Rx languages. The Tee method looks like this:

 
 
public static class FunctionalExtensions {

	public static T Tee<T>(this T @this, Action<T> act) {
		act(@this);
		return @this;
	}
	
}

 

We can now inspect state in the middle of chained expressions in functional expressions.

 
 
 
private static string BuildSelectBox(IDictionary<int, string> options, string id, bool includeUnknown) =>
		new StringBuilder()
			.AppendFormattedLine($"<select id=\"{id}\" name=\"{id}\">")
			.AppendWhen(() => includeUnknown, sb => sb.AppendLine("\t<option value=\"0\">Unknown</option>"))
            .Tee(Console.WriteLine)
			.AppendSequence(options, (sb, item) => sb.AppendFormattedLine("\t<option value=\"{0}\">{1}</option>", item.Key, item.Value))
			.AppendLine($"</select>").ToString();   
 
 

The picture below shows the output:

So there you have it, if you have lengthy chained functional expressions, make such a Tee helper method to peek into the state this far. The name Tee stems from the Unix Command by the same name. It copies contents from STDIN to STDOUT. More about Tee Unix command here:

https://shapeshed.com/unix-tee/