Introduction

As we are writing business / utility applications sometimes we need to store complex types directly in the database as a column in a format of bytes, xml, json, etc..,. Here I am going to explain how to as a json data inside postgresql database.

What is JSON / JSONB in PostgreSQL

It is a datatype, a representation of JSON data in the PostgreSQL database. JSON and JSONB both are used to store JSON data. Here JSONB datatype has more validation and data accessing techniques / methods. For more details check here.

In many cases we might want to maintain the data in single column as JSON. For example a API response from any of the services like payment responses from payment gateway. These data information not really need to be presented in the traditional way of keeping columns for each detail, because of it will increase the table count and complexity of the database.

JSONB with Dapper

using JSONB directly because it is more advanced then JSON in PostgreSQL environment.

Storing / accessing the JSONB data from the database is not simple as I thought of initially. Because of that complexity, Initially I used string in DotNet side and JSONB in database side. For this I need to serialize the data to string when I am going to store it, and do the deserialization when I want it. In the search of solution for this problem I found that I can use Type handlers of Dapper to solve this problem. This idea found from this blog.

What is Type Handlers

This is a concept given by Dapper to handle custom types, Type Handlers allow database types to be converted to DotNet custom types like class, dictionary, etc.

Defining Type Handler

To define the type handler we have to inherit base class TypeHandler<T> and implement the Parse and SetValue methods.

• Parse: Used to parse the column value to DotNet type while reading from DB
• SetValue: Used to set the value to the column parameter when storing it (insert / update).

Here I am using PaymentResponse class as the C# representation of the data stored in the payment history table with column name paymentgatewayresponse. Not important and Assume that the class has some properties like paid amount, payment type, payment started at, ended at, etc..,

Type Handler for the specific Type PaymentRespose is PaymentResponseJsonBTypeHandler, The implementation will be

    
public class PaymentResponseJsonBTypeHandler : TypeHandler<PaymentResponse>
{
  public override T Parse(object value)
  {
    if (value == null || value == DBNull.Value)
    {
      return default;
    }

    // I used newtonsoft json serializer here
    return JsonConvert.DeserializeObject<PaymentResponse>(value.ToString());
  }

  public override void SetValue(IDbDataParameter parameter, PaymentResponse value)
  {
    // I used newtonsoft json serializer here
    parameter.Value = JsonConvert.SerializeObject(value);

    //Here we have to use different type settings for the desired database
    //I am using postgresql so the NpgsqlDbType enum used.
    if (parameter is NpgsqlParameter npgsqlParameter)
    {
      npgsqlParameter.NpgsqlDbType = NpgsqlDbType.Jsonb;
    }
  }
}
  

Once I defined the type handler for the type then I have to tell the dapper to use it, For that I have to register it

    
var paymentReposenseTypeHandlerInstance = new PaymentResponseJsonBTypeHandler();
SqlMapper.AddTypeHandler(paymentReposenseTypeHandlerInstance);
  

In this approach, I have to register each one type separately like above which all are going to be used to read data from the database. It won't helpful. so I tried to find the best way, Our friend Generics can help here. Means if I make the Type handler as generic then the problem half solved, so the changed code will looks like

    
public class JsonBTypeHandler<T> : TypeHandler<T>
{
  public override T Parse(object value)
  {
    if (value == null || value == DBNull.Value)
    {
      return default;
    }

    // I used newtonsoft json serializer here
    return JsonConvert.DeserializeObject<T>(value.ToString());
  }

  public override void SetValue(IDbDataParameter parameter, T value)
  {
    // I used newtonsoft json serializer here
    parameter.Value = JsonConvert.SerializeObject(value);

    //Here we have to use different type settings for the desired database
    //I am using postgresql so the NpgsqlDbType enum used.
    if (parameter is NpgsqlParameter npgsqlParameter)
    {
      npgsqlParameter.NpgsqlDbType = NpgsqlDbType.Jsonb;
    }
  }
}
  

To register the type handler for all types, again I need to do the same, It can be solved by may ways I took inheritance with a common Interface. So whoever, inheriting the interface can simply be a type that can be used to Hold data of JSONB

    
public interface IJsonBData { }
  

Once I created this, Then the JSONB type handler can be registered for all types by who inherits IJsonBData interface by

    
//Collecting all types who has inherited from IJSonBData
var executingAssemblyTypes = Assembly
  .GetExecutingAssembly()
  .GetTypes()
  .Where(t => t.IsClass && !t.IsAbstract && typeof(IJsonBData).IsAssignableFrom(t));

//Registering All type handlers to the Dapper
foreach (var type in executingAssemblyTypes)
{
  var genericType = typeof(JsonBTypeHandler<>).MakeGenericType(type);
  var instance = (ITypeHandler)Activator.CreateInstance(genericType);
  AddTypeHandler(type, instance);
}
  

The PaymentResponse class will looks like

    
public class PaymentResponse : IJsonBData
{
  //All available paramters
}
  

Now, I can read the Payment Response from history table as whole entity.

    
dbConnectionInstance.Query<PaymentHistory>("select * from paymenthistory")
  

Or, I can read only the JSONB content,

    
// Reading all
dbConnectionInstance.Query<PaymentResponse>("select paymentgatewayresponse from paymenthistory")

//Reading One Row
dbConnectionInstance.QueryOne<PaymentResponse>("select paymentgatewayresponse from paymenthistory limit 1")
  

Or, I can read the required individual value, This is not related to the Handler

    
var query = "select (paymentgatewayresponse ->> 'PaidAmount')::numeric from paymenthistory";
dbConnectionInstance.Query<decimal>(query)
  

Reading the JSONB into Dictionary

This is only possible to have when we have a type that inherits from dictionary and IJsonBData. or register the dictionary directly to the Dapper. something like

    
SqlMapper.AddTypeHandler(typeof(Dictionary<string, string>), new Dictionary<string, string>());
  

Conclusion

Here I have explained about how I solved my problem of reading a JSONB content from PostgreSQL into dotnet complex type or A property of a class / entity. It is possible to have a single entity / table / class having multiple JSONB data. This still will work. Also it will handle the situation of list of entity stored in the column.

Nuget

From this understanding i have developed a library and released it in Nuget.

Package Availabled At : Nuget Package

Package Souce Code : Github Source

Thank you…

Written On : July 25th 2023