A Simple Analogy

JSON columns are like drawers within drawers. Instead of a completely flat cabinet (relational), you group related items in JSON, making queries more flexible and intuitive.

---

Why JSON Columns?

• Flexible schema: Store semi-structured data
• Query-able: Filter and project JSON content
• Denormalization: Avoid complex joins
• Performance: Cache related data together
• Compatibility: Mix relational and document data

---

Configuration

public class User
{
    public int Id { get; set; }
    public string Name { get; set; }
    
    // JSON column
    public Address Address { get; set; }
    public List<Phone> PhoneNumbers { get; set; }
}

public class Address
{
    public string Street { get; set; }
    public string City { get; set; }
    public string ZipCode { get; set; }
}

public class Phone
{
    public string Type { get; set; }
    public string Number { get; set; }
}

// DbContext configuration
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<User>()
        .Property(u => u.Address)
        .HasColumnType("jsonb");  // PostgreSQL
    
    modelBuilder.Entity<User>()
        .Property(u => u.PhoneNumbers)
        .HasColumnType("json");
}

---

Querying JSON

// Filter by JSON property
var users = await context.Users
    .Where(u => u.Address.City == "New York")
    .ToListAsync();

// Project JSON property
var cities = await context.Users
    .Select(u => u.Address.City)
    .ToListAsync();

// Filter JSON array
var usersWithHomePhone = await context.Users
    .Where(u => u.PhoneNumbers.Any(p => p.Type == "Home"))
    .ToListAsync();

// Complex queries
var result = await context.Users
    .Where(u => u.Address.City == "NYC" && 
               u.PhoneNumbers.Count > 1)
    .Select(u => new {
        u.Name,
        u.Address.ZipCode,
        HomePhones = u.PhoneNumbers
            .Where(p => p.Type == "Home")
            .ToList()
    })
    .ToListAsync();

---

JSON Operators (SQL Server)

// Using SQL.Invoke for advanced operators
var query = context.Users
    .Where(u => EF.Functions.JsonExtract(
        u.Address, "$.City") == "New York");

// Or with value conversions
var users = await context.Users
    .FromSqlInterpolated($@"
        SELECT * FROM Users 
        WHERE Address->>'City' = {city}")
    .ToListAsync();

---

Practical Example

public class Order
{
    public int Id { get; set; }
    public int CustomerId { get; set; }
    
    // Nested JSON object
    public BillingInfo BillingInfo { get; set; }
    
    // JSON array
    public List<LineItem> Items { get; set; }
}

public class BillingInfo
{
    public Address Address { get; set; }
    public PaymentMethod PaymentMethod { get; set; }
}

public class PaymentMethod
{
    public string Type { get; set; }  // CreditCard, Bank, etc.
    public string LastDigits { get; set; }
}

public class LineItem
{
    public string ProductId { get; set; }
    public int Quantity { get; set; }
    public decimal Price { get; set; }
}

// Queries
var creditCardOrders = await context.Orders
    .Where(o => o.BillingInfo.PaymentMethod.Type == "CreditCard")
    .ToListAsync();

var expensiveOrders = await context.Orders
    .Where(o => o.Items.Sum(i => i.Price * i.Quantity) > 1000)
    .ToListAsync();

---

Performance Considerations

// Index JSON column (PostgreSQL)
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<User>()
        .HasIndex("Address->>'City'")
        .HasName("idx_address_city");
    
    // Or on array elements
    modelBuilder.Entity<Order>()
        .HasIndex("Items->'$.ProductId'")
        .HasName("idx_product_ids");
}

// Use computed properties
public class User
{
    public int Id { get; set; }
    public Address Address { get; set; }
    
    [NotMapped]
    public string City => Address?.City;
}

---

Best Practices

1. Use for semi-structured: Not for frequently-queried relations
2. Don't over-normalize: Keep related data together
3. Index JSON paths: Query performance
4. Validate on insert: Ensure data shape
5. Document schema: JSON structure contracts

---

Related Concepts

• Document databases (MongoDB)
• Denormalization strategies
• JSON schema validation
• Full-text search

---

Summary

JSON columns in EF Core provide flexible storage and querying of semi-structured data. Use them to store related objects without complex normalization while maintaining queryability.