MySQL Document Store

 MySQL Document Store

MySQL Document Store allows developers to work with SQL relational tables and schema-less JSON collections. To make that possible MySQL has created the X Dev API which puts a strong focus on CRUD by providing a fluent API allowing you to work with JSON documents in a natural way. The X Protocol is a highly extensible and is optimized for CRUD as well as SQL API operations.

NoSQL + SQL = MySQL

MySQL Document store gives users maximum flexibility developing traditional SQL relational applications and NoSQL schema-free document database applications. This eliminates the need for a separate NoSQL document database. Developers can mix and match relational data and JSON documents in the same database as well as the same application. For example, both data models can be queried in the same application and results can be in table, tabular or JSON formats.

A Guide to Transforming Documents into Data:-

MySQL Document Store enables you to work with JSON documents using MySQL. It combines the flexibility of a NoSQL document store with the strength of a relational database. This approach allows you to store and manage unstructured data (documents) while still leveraging the robust capabilities of MySQL. Here's how you can turn documents into data using MySQL Document Store:

1. Set Up MySQL Document Store

• Install MySQL 8.0 or Higher: Ensure you have MySQL 8.0 or a later version, as the Document Store feature is available from MySQL 8.0 onwards.
• Enable X Plugin: The MySQL X Plugin must be enabled to use Document Store. It usually is enabled by default in MySQL 8.0.

2. Connect to MySQL Using X DevAPI

• Use X DevAPI: Connect to MySQL using the X DevAPI, which provides an API for working with documents in MySQL.
• Client Libraries: Use client libraries for X DevAPI in languages like JavaScript, Python, .NET, Java, etc.

3. Create a Schema and Collection

• Create Schema: In Document Store terminology, a schema is similar to a database in traditional MySQL usage.

              CREATE SCHEMA my_document_db;

• Create Collection: A collection in the Document Store is akin to a table. It stores JSON documents.

             CREATE COLLECTION my_collection;

4. Insert Documents

• JSON Documents: Insert JSON documents into the collection. Each document can have a different structure.

           INSERT INTO my_collection (doc) VALUES ('{"name": "John", "age": 30}');

5. Querying Documents

• CRUD Operations: Use X DevAPI for CRUD (Create, Read, Update, Delete) operations on JSON documents.
• SQL and NoSQL: You can use both SQL and NoSQL queries. For example, use find() in X DevAPI to retrieve documents.

6. Utilize JSON Functions

• MySQL JSON Functions: Leverage MySQL's built-in JSON functions for manipulating JSON data within your documents.

7. Indexing JSON Documents

• Create Indexes: To improve query performance, create indexes on JSON document fields.

           CREATE INDEX idx_name ON my_collection( (CAST(doc->>'$.name' AS CHAR(50)) ) );

8. Update and Delete Documents

• Modify Data: Use X DevAPI methods or SQL queries to update or delete documents in your collections.

9. Integrate with Applications

• Application Integration: Easily integrate the Document Store with your applications using the respective language connectors.

10. Leverage Relational Features

• ACID Compliance: Benefit from the ACID compliance of MySQL even while using the Document Store.
• Join JSON and Relational Data: You can join JSON documents with traditional relational tables.

Conclusion

MySQL Document Store offers a flexible way to work with JSON documents, combining NoSQL's ease of handling unstructured data with the robust features of MySQL. By utilizing the X DevAPI, developers can easily perform operations on JSON documents, benefiting from the strong consistency, reliability, and performance of MySQL. This approach is particularly beneficial for applications that require the flexibility of a document model along with the integrity and capabilities of a relational database.