Structured Semi-Structured Data!
In this article, we are going to explore storing semi-structured data (JSON) in a structured Relational Database Management System (RDBMS).
It is often thought that you require a specialist database engine for storage of JSON data. When people think of JSON data they often think of document-oriented, or NoSQL, databases such as MongoDB, Apache CouchDB and amongst others the offerings from Amazon and Azure. There are many advantages to using dedicated document-oriented databases, scalability, flexibility and the ability to store large amounts of unstructured, or semi-structured data. For some use cases, these may be the ideal fit.
What if you require structure to your semi-structured data, what if you need ACID compliance, what if you would like your developers to access the data via standard tools, programming connectors and SQL statements. What if your ‘traditional’ RDBMS database could hold both types of data, provide column-store storage engines and the ability to shard, partition and cluster your data over many servers or even docker containers.
Is it possible to have “Structured and Semi-Structured” and “Analytical and Transactional” data combined within one database engine? The answer, of course, is yes.
Using MariaDB you can load and manipulate JSON data into a structured RDBMS.
MariaDB is a modern database engine which comes with a whole range of functions for handling and manipulating your JSON data, from checking the data is valid on import JSON_VALID, removing white space JSON_COMPACT or even adding space JSON_LOOSE to make it more readable. For the full list of commands, you can view the MariaDB JSON knowledge base.
As a fully functioning database engine, many things are possible with your JSON data. You can add “virtual” columns to be a particular value from the JSON data, for example, name. People are often surprised to hear that you can index this data, allowing you to sort and query the data straight from your application and using standard SQL commands.
To demonstrate how to import JSON data into MariaDB and manipulate it, I have created an accompanying video which you can view on YouTube.
