Why MySQL 8.0’s Document Store Makes NoSQL Powerful: Top 10 Benefits

Introduction

MySQL 8.0 adds a native document store that lets JSON documents be kept in collections and accessed through the X DevAPI. The store runs on the InnoDB engine, so every operation is fully ACID‑compliant and can be combined with traditional SQL queries.

Prerequisites

Since MySQL 8.0.11 the X Plugin is installed by default, providing the X Protocol and X DevAPI. All official MySQL connectors (including the PHP connector) support these interfaces.

Key technical advantages

ACID‑compliant NoSQL Because the document store uses InnoDB, default settings guarantee durability:

innodb_flush_log_at_trx_commit = 1

innodb_doublewrite = ON

sync_binlog = 1

Example of a transaction that can be rolled back:

MySQL [localhost+ ssl/docstore] JS> db.users.find()
[ { "_id": "00005ad754c90000000000000001", "name": "lefred" } ]
MySQL [localhost+ ssl/docstore] JS> session.startTransaction()
Query OK, 0 rows affected
MySQL [localhost+ ssl/docstore] JS> db.users.add({name: 'dim0'})
Query OK, 1 item affected
MySQL [localhost+ ssl/docstore] JS> db.users.find().fields('name')
[ { "name": "lefred" }, { "name": "dim0" } ]
MySQL [localhost+ ssl/docstore] JS> session.rollback()
Query OK, 0 rows affected
MySQL [localhost+ ssl/docstore] JS> db.users.find().fields('name')
[ { "name": "lefred" } ]

CRUD without mandatory SQL Collections expose simple functions that map directly to SQL statements:

add() → INSERT

find() → SELECT

modify() → UPDATE

remove() → DELETE

Schema‑less storage Documents can be stored without a predefined schema, and fields can be added or changed at any time. Example of adding a new attribute to all users without ALTER:

MySQL [localhost+ ssl/docstore] JS> db.users.modify('1').set('department', 'development')
Query OK, 5 items affected
MySQL [localhost+ ssl/docstore] JS> db.users.find("name = 'Sunny'")
[ { "_id": "00005ad754c90000000000000006", "department": "development", "name": "Sunny" } ]

Data‑integrity features Generated columns and foreign‑key constraints can be defined on JSON documents. The following creates generated columns and a cascade‑delete foreign key:

ALTER TABLE departments ADD COLUMN dept VARCHAR(20) GENERATED ALWAYS AS (doc->>'$.name') STORED;
ALTER TABLE users ADD COLUMN dept VARCHAR(20) GENERATED ALWAYS AS (doc->>'$.department') STORED;
ALTER TABLE users ADD FOREIGN KEY (dept) REFERENCES departments(dept) ON DELETE CASCADE;

Deleting a department automatically removes related users.

SQL still available SQL queries can be mixed with NoSQL collections. Example using a CTE to rank the best restaurant per cuisine:

WITH cte AS (
  SELECT doc->>'$.name' AS name,
         doc->>'$.cuisine' AS cuisine,
         (SELECT AVG(score) FROM JSON_TABLE(doc, '$.grades[*]' COLUMNS (score INT PATH '$.score')) AS r) AS avg_score
  FROM restaurants
)
SELECT *, RANK() OVER (PARTITION BY cuisine ORDER BY avg_score) AS rank
FROM cte
ORDER BY rank, avg_score DESC
LIMIT 10;

Large document support Individual documents can be up to 1 GB, limited only by max_allowed_packet , removing the typical 16 MB ceiling of many NoSQL systems.

Simple query syntax MySQL Shell provides concise commands. The same query in MongoDB and MySQL Shell illustrates the brevity of MySQL syntax:

MongoDB:
> db.restaurants.find({"cuisine": "French", "borough": { $not: /^Manhattan/} }, {"_id":0, "name":1,"cuisine":1, "borough":1}).limit(2)

MySQL Shell:
MySQL [localhost+ ssl/docstore] JS> restaurants.find("cuisine='French' AND borough!='Manhattan'").fields(["name","cuisine","borough"]).limit(2)

Built‑in security MySQL generates a strong temporary root password and supports SSL‑encrypted sessions out of the box:

2018-06-14T12:47:55.120634Z 5 [Note] A temporary password is generated for root@localhost: (hdirvypB1iw)

mysqlsh [email protected] --ssl-mode=REQUIRED

Simplified infrastructure A single MySQL instance can serve both relational tables and document collections, eliminating the need for separate NoSQL clusters.

Leverage existing DBA expertise DBAs can manage, tune, and scale the document store using the same tools and knowledge they already have for MySQL.

Conclusion

MySQL 8.0’s document store unifies SQL and NoSQL in a single, ACID‑compliant engine. It offers schema‑less JSON storage, powerful CRUD APIs, support for large documents, built‑in security, and the ability to run mixed SQL/NoSQL workloads without additional infrastructure.