The SQL vs NoSQL Debate

NoSQL, SQL, or NewSQL? Those database types and the concepts they're based on are not mutually exclusive, even though some dogmatists try to make you think otherwise. Modern SQL databases have evolved to support JSON data types, giving you the best of both worlds.

Instead of forcing every data structure into normalized tables or jumping to a NoSQL solution, consider making compromises within your SQL database first. The fewer moving parts a system consists of, the better. Sometimes the simple solution is to store JSON directly in SQL.

Example 1: Complex Data Structures

Consider a system where users can have different role types with varying access levels. Some users are admins or managers with full access, while clerks might have either unrestricted access or restricted access to specific clients only.

How would you model this in a traditional relational database? You could create multiple tables with foreign keys and join queries. But this approach leads to complex queries and multiple database round trips.

The Traditional Approach

-- User table with role
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    role_type INT  -- 1=Admin, 2=Manager, 3=Clerk
);

-- Client access for restricted clerks
CREATE TABLE user_client_access (
    user_id INT,
    client_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Checking access requires multiple queries
SELECT * FROM users WHERE id = ?;
SELECT client_id FROM user_client_access WHERE user_id = ?;

The JSON Approach

Now consider storing the role information as JSON alongside the user record. This allows you to serialize complex role structures and deserialize them instantly after fetching the user record. You only need to make that one query.

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    role JSON  -- Stores complex role structure
);

-- Single query to get everything
SELECT * FROM users WHERE id = ?;

-- Example JSON stored:
{
  "type": "clerk",
  "access": {
    "type": "restricted",
    "clientIds": [1, 5, 12]
  }
}

Example 2: Variable Data Structures

Another use case is when you don't know the exact data structure upfront or when it needs to be extended or changed over time. Instead of creating multiple tables or altering schema frequently, you can store variable data in JSON format.

Action Logging Example

Consider a system that logs various user actions over time. Each action might have a different structure depending on its type. Trying to normalize this into separate columns would be impractical.

CREATE TABLE user_account_actions (
    id INT PRIMARY KEY,
    user_id INT,
    timestamp TIMESTAMP,
    action_type VARCHAR(50),
    action_data JSON  -- Stores variable action details
);

-- Example action data stored as JSON:
{
  "type": "masterDataUpdated",
  "changes": {
    "email": "old@example.com -> new@example.com",
    "role": "clerk -> manager"
  }
}

This approach allows you to store actions of any shape without having to create new columns for every possible action type. The database schema remains stable while your application can evolve.

Example 3: Solving the N+1 Problem

The notorious N+1 query problem occurs when you fetch a list of records and then need to make additional queries for each record to get related data. This is a common performance issue that JSON storage can solve elegantly.

The Classic N+1 Problem

Imagine you have a set of clients, and each client has its own set of features that can be turned on or off. The traditional approach would be:

-- Client table
CREATE TABLE clients (
    id INT PRIMARY KEY,
    name VARCHAR(255)
);

-- Client features junction table
CREATE TABLE client_features (
    client_id INT,
    feature_id INT,
    enabled BOOLEAN
);

-- Fetch clients (1 query)
SELECT * FROM clients;

-- Then for EACH client, fetch features (N queries)
SELECT * FROM client_features WHERE client_id = ?;

The JSON Solution

By serializing the features as JSON and storing them with the client, you can fetch everything in a single query. Reading the features becomes as easy as reading the client's name.

CREATE TABLE clients (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    features JSON  -- Stores feature flags
);

-- Single query gets everything
SELECT * FROM clients;

-- Example JSON:
{
  "feature1": true,
  "feature2": false,
  "feature3": true
}

JSON Is Not Just A Dumb String

Some may argue that JSON in SQL databases is just concatenated characters that you cannot query effectively. The reality is quite different with modern database systems.

PostgreSQL JSON Functions

PostgreSQL, for example, provides incredibly powerful JSON functions and operators that allow you to query and manipulate JSON data during the runtime of your queries.

-- Extract data from JSON
SELECT 
    id,
    name,
    role->>'type' as role_type,
    role->'access'->>'type' as access_type
FROM users;

-- Filter using JSON data
SELECT * FROM users 
WHERE role->>'type' = 'clerk';

-- Update JSON data
UPDATE users 
SET role = jsonb_set(
    role, 
    '{access,clientIds}', 
    '[1,2,3,4,5]'::jsonb
)
WHERE id = 1;

-- Query nested arrays
SELECT * FROM users
WHERE '123' = ANY(
    (role->'access'->>'clientIds')::jsonb
);

Other Database Support

• MySQL: JSON data types with functions for extracting and modifying JSON values
• SQL Server: JSON functions including OPENJSON and JSON_VALUE
• SQLite: JSON1 extension for JSON operations
• Oracle: Native JSON support with JSON_QUERY and JSON_VALUE functions

The Weak Spot: Schema Changes

Of course, it's not all perfect. While classic table columns can easily be transformed and migrated, JSON data requires more careful handling. When your application's data structures change, you must ensure backward compatibility.

Handling Schema Evolution

Here are some strategies for managing JSON schema changes:

When to Use JSON in SQL

Storing JSON in SQL databases works best when:

Conclusion

Storing JSON in SQL databases is not a compromise that weakens your database design. When consciously applied, JSON can be a valuable and powerful extension of the capabilities of your SQL database.

The key is knowing when to use JSON and when to stick with traditional relational structures. For complex data structures, variable schemas, and situations where you want to avoid N+1 queries, JSON storage can be the pragmatic and performant solution.

Modern SQL databases have excellent JSON support. PostgreSQL, MySQL, SQL Server, and others provide powerful JSON functions that let you query and manipulate JSON data just as you would with regular columns.