The Best of Both Worlds: Relational + JSON
As useful as JSON is for producing and consuming data, it used to require a NoSQL database to use. Not anymore! Let's see how you can use JSON in the SQL world.
Join the DZone community and get the full member experience.Join For Free
JSON is the de facto standard for consuming and producing data via web, mobile, and IoT services. JSON provides developers with a great deal of simplicity and flexibility, and in some cases, it may be more practical to store data as JSON documents in a database.
JSON documents required a NoSQL database in the past – a document database to be specific. However, NoSQL databases sacrificed transactions, data integrity and reliability (and a powerful query language) in order to deliver greater simplicity and flexibility.
That was then. This is now. You can use a relational database for JSON documents, and you no longer have to choose between relational and JSON. You can create hybrid data models comprised of structured and semi-structured data, and enjoy the benefits of JSON without sacrificing the advantages of a relational database (e.g., transactions and SQL).
MariaDB TX introduced SQL functions for creating and querying JSON documents. You can not only extend relational data with JSON documents, you can create JSON documents from rows or create rows from JSON documents. It’s up to you.
Let’s consider a couple of e-commerce examples. The first example involves product catalogs where every product has a name and price, yet different product types may have different attributes. You could create a separate table for every product type, but that’s not very flexible. It would be a lot easier to create a single table with columns for name (VARCHAR) and price (DECIMAL), and a separate column for attributes (JSON). A second example involves shopping carts where purchases may be stored as structured data with a fixed schema, but shopping carts can be stored as semi-structured with JSON documents. Using MariaDB TX, you can join customer profiles to purchases and/or shopping carts regardless of the format!
By introducing SQL functions for JSON, developers can now use a single database for both structured and semi-structured data – and administrators don’t have to worry about losing transactions, data integrity, and reliability. It’s the best of both worlds!
Published at DZone with permission of Amy Krishnamohan, DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.