Microsoft SQL Server JSON in 2025

You can read previous article “MySQL vs MariaDB: The Great JSON War” but once you see what SQL Server 2022 does with JSON, everything else looks like it’s from a completely different galaxy.

Here’s what real JSON support looks like when a company has been doing it seriously since 2016 and keeps improving it every quarter.

Real-life examples that hurt open-source fans

Storing flexible user profiles with instant indexing on nested fields

CREATE TABLE Users (
    Id        bigint IDENTITY PRIMARY KEY,
    Payload   json NOT NULL
              CONSTRAINT ValidJson CHECK (ISJSON(Payload) = 1),

    -- persisted computed columns – real narrow indexes, not virtual ones
    Age       AS CAST(JSON_VALUE(Payload, '$.profile.age') AS int) PERSISTED,
    Country   AS JSON_VALUE(Payload, '$.address.country') PERSISTED,
    Tags      AS JSON_VALUE(Payload, '$.tags') -- can be indexed with full-text later
);

CREATE INDEX IX_Users_Age ON Users(Age);
CREATE INDEX IX_Users_Country ON Users(Country) INCLUDE (Id);

CREATE TABLE Users (
    Id        bigint IDENTITY PRIMARY KEY,
    Payload   json NOT NULL
              CONSTRAINT ValidJson CHECK (ISJSON(Payload) = 1),

    -- persisted computed columns – real narrow indexes, not virtual ones
    Age       AS CAST(JSON_VALUE(Payload, '$.profile.age') AS int) PERSISTED,
    Country   AS JSON_VALUE(Payload, '$.address.country') PERSISTED,
    Tags      AS JSON_VALUE(Payload, '$.tags') -- can be indexed with full-text later
);

CREATE INDEX IX_Users_Age ON Users(Age);
CREATE INDEX IX_Users_Country ON Users(Country) INCLUDE (Id);

Do the same in MySQL → generated columns + secondary index. Do the same in MariaDB → same thing, but 4–10× slower in practice.

Real JSON path indexes (SQL Server 2022+)

CREATE INDEX IX_Orders_Country_Total 
ON Orders (JSON_VALUE(Data, '$.customer.country'))
INCLUDE (JSON_VALUE(Data, '$.totalAmount'));

CREATE INDEX IX_Orders_Country_Total 
ON Orders (JSON_VALUE(Data, '$.customer.country'))
INCLUDE (JSON_VALUE(Data, '$.totalAmount'));

This creates a real, narrow, seekable, updatable non-clustered index. MySQL’s equivalent is still a virtual column hack.

Building JSON without string concatenation nightmares

SELECT 
    Id,
    JSON_OBJECT(
        'id',      Id,
        'name',    FirstName + ' ' + LastName,
        'roles',   JSON_ARRAYAGG(Role) KEEP EMPTY ON NULL,
        'active',  IIF(IsActive = 1, JSON_TRUE(), JSON_FALSE())
    ) AS UserJson
FROM Users
GROUP BY Id, FirstName, LastName, IsActive
FOR JSON PATH, ROOT('users');

SELECT 
    Id,
    JSON_OBJECT(
        'id',      Id,
        'name',    FirstName + ' ' + LastName,
        'roles',   JSON_ARRAYAGG(Role) KEEP EMPTY ON NULL,
        'active',  IIF(IsActive = 1, JSON_TRUE(), JSON_FALSE())
    ) AS UserJson
FROM Users
GROUP BY Id, FirstName, LastName, IsActive
FOR JSON PATH, ROOT('users');

Zero manual commas, zero escaping, zero pain.

Updating a single field deep inside a 5-level JSON document

UPDATE Products
SET Data = JSON_MODIFY(Data, '$.specs.weight.kg', 2.7)
WHERE Id = 42;

UPDATE Products
SET Data = JSON_MODIFY(Data, '$.specs.weight.kg', 2.7)
WHERE Id = 42;

In most cases this is an in-place update, not a full LOB rewrite. Replication and CDC stay happy.

Turning any JSON into a relational set in one line

SELECT *
FROM OPENJSON(@json)
WITH (
    OrderId     bigint        '$.id',
    Customer    nvarchar(100) '$.customer.name',
    Total       decimal(12,2) '$.total',
    Items       nvarchar(max) '$.items' AS JSON
);

SELECT *
FROM OPENJSON(@json)
WITH (
    OrderId     bigint        '$.id',
    Customer    nvarchar(100) '$.customer.name',
    Total       decimal(12,2) '$.total',
    Items       nvarchar(max) '$.items' AS JSON
);

No loops, no temporary tables, no tears.

Full-text search inside JSON (because why not)

CREATE FULLTEXT INDEX ON Documents(Data) KEY INDEX PK_Documents;
SELECT * FROM Documents
WHERE CONTAINS(Data, 'NEAR(("machine learning", "production"), 10)');

CREATE FULLTEXT INDEX ON Documents(Data) KEY INDEX PK_Documents;
SELECT * FROM Documents
WHERE CONTAINS(Data, 'NEAR(("machine learning", "production"), 10)');

Try doing that cleanly in MySQL or MariaDB.

Performance reality check (2025 numbers, same 64-core Epyc box)

The price myth

SQL Server Express is still free and the only real limit is 10 GB per database (not per instance). All JSON features above work 100% in Express.

So if your architecture can split data across databases (per tenant, per year, per customer, per region – basically any modern microservice design), you pay exactly $0.00 forever.

Minor downsides (being honest)

• Still no native multi-valued indexes (MySQL wins that tiny battle)
• No $** recursive descent yet (you use OPENJSON + CROSS APPLY)
• JSON Schema validation exists but is opt-in and a bit verbose

Final knockout punch

If your application lives on semi-structured JSON and you actually care about performance, flexibility, and developer experience in 2025, Microsoft SQL Server is so far ahead of MySQL and MariaDB that comparing them feels unfair.

And thanks to SQL Server Express, even the “it costs money” excuse is gone.

Original MySQL vs MariaDB fight: https://4coders.own.pl/databases/mariadb-vs-mysql-the-great-json-war/

Spoiler: after declaring an open-source winner, I discovered the real champion never even entered the ring… because it was busy winning for the last eight years.

Welcome to SQL Server. Game over