Storing JSON data in relational databases using EF Core

JavaScript Object Notation (JSON) is a great fit when you are working with logs, configuration settings, or other flexible data structures. JSON represents data in a human-readable format that many modern applications can parse easily. In this article, you will learn how to store and manage JSON data in relational databases using EF Core. We will also review the real code implementations for each primary database: PostgreSQL, SQL Server, and MySQL.

What is JSON?

JSON is a lightweight text-based format designed to represent data in key-value structured objects. It is widely used for storing and exchanging information in web and mobile applications, APIs, databases, and anywhere flexibility is required without rigid schemas. Whether you are a front-end or back-end developer, you are not hearing JSON for the first time. 

Returning to the article's main point - how to store JSON in relational databases using EF Core. I will use a console project to keep things as simple as possible for all examples.

How to store JSON in PostgreSQL database? 

PostgreSQL offers the jsonb type for JSON values storage, supporting indexing and querying. Let's see how this can be implemented.

Step 1: Install NuGet packages

dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL
dotnet add package Microsoft.EntityFrameworkCore.Design

Step 2: Define Models

public class LogEntry
{
    public int Id { get; set; }
    public LogDetail? Details { get; set; }
}

public class LogDetail
{
    public string? Level { get; set; }
    public string? Message { get; set; }
    public DateTime Timestamp { get; set; }
}

Step 3: Set AppDbContext

using JsonDbStore.Models;
using Microsoft.EntityFrameworkCore;

namespace JsonDbStore;

using System.Text.Json;

public class AppDbContext: DbContext
{
    public DbSet<LogEntry> Logs => Set<LogEntry>();

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseNpgsql("actualConnectionString");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<LogEntry>(entity =>
        {
            entity.Property(e => e.Details)
                .HasColumnType("jsonb")
                .HasConversion(
                    v => JsonSerializer.Serialize(v, new JsonSerializerOptions()),
                    v => JsonSerializer.Deserialize<LogDetail>(v, new JsonSerializerOptions())
                );
        });
    }
}

In this example, I configured the Details property to be saved as jsonb, PostgreSQL's data type for saving blob data as JSON.

Step 4: Configure Program.cs

using JsonDbStore;
using JsonDbStore.Models;

using var db = new AppDbContext();

// Insert sample JSON data
db.Logs.Add(new LogEntry
{
    Details = new LogDetail
    {
        Level = "Info",
        Message = "Application started",
        Timestamp = DateTime.UtcNow
    }
});
db.SaveChanges();

// Query JSON data
var infoLogs = db.Logs
    .Where(l => l.Details!.Level == "Info")
    .ToList();

foreach (var log in infoLogs)
{
    Console.WriteLine($"[{log.Details!.Level}] {log.Details.Message} at {log.Details.Timestamp}");
}

The logs create a new database context and use it to insert a new LogEntry. Then create a query and output the results.

Step 5: Apply migration and update the database

dotnet tool install --global dotnet-ef
dotnet ef migrations add InitJsonTable
dotnet ef database update

Step 6: Run the project

The program prints the database data to the console. The interesting part is when inspecting the database using a SQL query to show the embedded JSON in the Details column:

How to store JSON in an MSSQL database?

MSSQL uses a regular nvarchar(max) column to save JSON. EF Core handles serialisation automatically through the HasConversion logic that I will show next.

Step 1: Install NuGet packages

dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Microsoft.EntityFrameworkCore.Design

Step 2: Define models

I will re-use the LogEntry class from the previous example.

Step 3: Create AppDbContext

using Microsoft.EntityFrameworkCore;
using JsonEfSqlServerDemo.Models;
using System.Text.Json;

public class AppDbContext: DbContext
{
    public DbSet<LogEntry> Logs => Set<LogEntry>();

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer("actualConnectionString");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<LogEntry>(entity =>
        {
            entity.Property(e => e.Details)
                  .HasColumnType("nvarchar(max)")
                  .HasConversion(
                      v => JsonSerializer.Serialize(v, new JsonSerializerOptions()),
                      v => JsonSerializer.Deserialize<LogDetail>(v!, new JsonSerializerOptions())
                  );
        });
    }
}

As mentioned, we can use the HasConversion method to map the Details property to JSON.

Step 4: Set up Program.cs

using var db = new AppDbContext();

// Insert JSON data
db.Logs.Add(new LogEntry
{
    Details = new LogDetail
    {
        Level = "Error",
        Message = "Failed to connect",
        Timestamp = DateTime.UtcNow
    }
});

db.SaveChanges();

// Query and deserialise
var errorLogs = db.Logs
    .Where(l => l.Details!.Level == "Error")
    .ToList();

foreach (var log in errorLogs)
{
    Console.WriteLine($"[{log.Details!.Level}] {log.Details.Message} at {log.Details.Timestamp}");
}

Step 5: Run migrations

Same as in the previous example.

Step 6: Run and test the app

The program prints

How to store JSON in MySQL database?

MySQL provides the json data type to store the key-value data. The data type keeps JSON as a strongly typed value that supports indexing and querying JSON paths (e.g., ->, ->>, JSON_EXTRACT()).

Step 1: Install NuGet packages

dotnet add package Pomelo.EntityFrameworkCore.MySql
dotnet add package Microsoft.EntityFrameworkCore.Design

Step 2: Create model classes

Same as the previous examples.

Step 3: Create AppDbContext

using Microsoft.EntityFrameworkCore;
using System.Text.Json;

public class AppDbContext: DbContext
{
    public DbSet<LogEntry> Logs => Set<LogEntry>();

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseMySql(
            "actualConnectionString",
            new MySqlServerVersion(new Version(8, 0, 32)) // Specifying the version. Replace with your version
        );
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<LogEntry>(entity =>
        {
            entity.Property(e => e.Details)
                  .HasColumnType("json")
                  .HasConversion(
                      v => JsonSerializer.Serialize(v, new JsonSerializerOptions()),
                      v => JsonSerializer.Deserialize<LogDetail>(v!, new JsonSerializerOptions())
                  );
        });
    }
}

The Details column will use the json datatype.

Step 4: Set up Program.cs

using var db = new AppDbContext();

// Insert JSON data
db.Logs.Add(new LogEntry
{
    Details = new LogDetail
    {
        Level = "Warning",
        Message = "Disk space low",
        Timestamp = DateTime.UtcNow
    }
});

db.SaveChanges();

// Read data
var warningLogs = db.Logs
    .Where(l => l.Details!.Level == "Warning")
    .ToList();

foreach (var log in warningLogs)
{
    Console.WriteLine($"[{log.Details!.Level}] {log.Details.Message} at {log.Details.Timestamp}");
}

Step 5: Run migrations

Same as above.

Step 6: Test the implementation

Conclusion

JSON is a popular data format used for API-based communication and configuration settings due to its lightweight, readability, and ease of parsing. You may need to save the complete JSON object, keeping it intact, directly in your database. Modern relational databases provide support for JSON types. We discussed saving the key-value format in PostgreSQL, MSSQL, and MySQL databases.

See how we can help you monitor your website for crashes Monitor your website