Hybrid Relational/JSON Data Modeling and Querying

Section 1

Introduction

JSON has become the de facto standard for sending and receiving data — in particular, between application servers and the browsers/mobile applications connected to them. In the late 2000s, document databases such as MongoDB were built to store and query JSON documents.

Today, relational databases can store and query data as relations (i.e., rows and columns) or JSON — and with standard SQL.

There are a few ways to take advantage of the JSON support within relational databases:

Storing data as relations, querying it as JSON documents.
Storing data as JSON documents, querying it as relations.
Storing data as relations with JSON documents, querying it as relations and/or JSON documents.

The third approach, hybrid relational/JSON data modeling and querying, is covered in this DZone Refcard.

This is a preview of the Hybrid Relational/JSON Data Modeling and Querying Refcard. To read the entire Refcard, please download the PDF from the link above.

Section 2

Hybrid Relational/JSON Overview

Extending relations with JSON documents allows easier, faster application development while at the same time maintaining the operational benefits of relational databases — transactions, data integrity, and relationships.

Data Modeling

Attributes common to all objects should be stored as relations. The examples in this DZone Refcard use a simple product catalog table for both movies and books. Every product has a name and price, so the product table has name and price columns.

However, whereas books have authors, movies have resolutions. Thus, these properties are stored in a separate JSON column. In addition, a JSON column allows easy addition of new properties in the future and properties with multiple and/or complex values.

For example, a movie may have multiple cuts, with each cut having its own title and running time. Because cuts are unique to a movie, it may not make sense to store them in a separate table with a one-to-many relationship.

Data Querying

JSON documents are queried with SQL functions. The core functions (e.g., reading a field within a JSON document) are defined in the SQL:2016 standard. With these functions, queries can access both relations and JSON documents at the same time.

JSON functions often take one or more the following arguments:

The name of the column containing the JSON document.
One or more JSONPath expressions to fields within the JSON document.
A value (if searching fields or inserting/updating a field).

This is a preview of the Hybrid Relational/JSON Data Modeling and Querying Refcard. To read the entire Refcard, please download the PDF from the link above.

Section 3

Data Modeling and Querying in MariaDB Server

MariaDB Server implements all JSON functions defined in the SQL:2016 standard and includes additional ones that may or may not be implemented in other relational databases. The examples in this Refcard use the SQL JSON functions available in MariaDB Server 10.4. Sections include:

Inserting sample data
Reading, creating, updating, and searching fields
Reading, creating, and updating arrays
Reading and creating objects
Indexing fields
Integrity constraints

This is a preview of the Hybrid Relational/JSON Data Modeling and Querying Refcard. To read the entire Refcard, please download the PDF from the link above.

Section 4

Quick Reference: MariaDB Server SQL JSON Functions

Function	Description	SQL:2016
`JSON_ARRAY`	Create a JSON array from one or more key/value pairs	Y
`JSON_ARRAYAGG`		Y
`JSON_ARRAY_APPEND`	Append an element to a JSON array	N
`JSON_ARRAY_INSERT`	Insert an element into a JSON array	N
`JSON_COMPACT`	Remove unnecessary white space from a JSON document	N
`JSON_CONTAINS`	See if a JSON document or field contains a specific value	N
`JSON_CONTAINS_PATH`	See if a JSON document contains one or more specific fields	N
`JSON_DEPTH`	Get the maximum depth of a JSON document	N
`JSON_DETAILED`	Format a JSON document so it is easy to read	N
`JSON_EXISTS`	See if a JSON document contains a specific field	Y
`JSON_EXTRACT`	Get one or more specific fields from a JSON document	N
`JSON_INSERT`	Insert a field into a JSON document	N
`JSON_KEYS`	Get all of the keys within a JSON document or a nested JSON object	N
`JSON_LENGTH`	Get the length of a JSON document or the value of a specific field	N
`JSON_MERGE_PATCH`	Merge two or more JSON documents	N
`JSON_OBJECT`	Create a JSON object from one or more key/value pairs	Y
`JSON_OBJECTAGG`		Y
`JSON_QUERY`	Get the value of a specific JSON object or array within a JSON document	Y
`JSON_QUOTE`	Create a JSON string from a string	N
`JSON_REMOVE`	Remove a field from a JSON document	N
`JSON_REPLACE`	Replace the value of a specific field within a JSON document	N
`JSON_SEARCH`	Get the first or all paths to fields containing a specific value	N
`JSON_SET`	Insert or update the value of a specific field within a JSON document	N
`JSON_TABLE` *	Get a JSON document as a table with specific columns	Y
`JSON_TYPE`	Get the type of a specific field within a JSON document	N
`JSON_UNQUOTE`	Create a string from a JSON string	N
`JSON_VALID`	See if the JSON document is valid JSON	N
`JSON_VALUE`	Get value of a specific JSON string, number of boolean within a JSON document	Y

*The JSON_TABLE function is planned for MariaDB Server 10.5 (beta as of March 2020).

This is a preview of the Hybrid Relational/JSON Data Modeling and Querying Refcard. To read the entire Refcard, please download the PDF from the link above.

Section 5

Conclusion

Today, JSON is supported within relational databases and via standard SQL. With existing databases, even if they are fully normalized, SQL queries can be written (or rewritten) to return JSON documents instead of rows and columns, simplifying the development of web and mobile applications that use JSON to send and receive data.

To make it even easier, SQL JSON functions can be used to create views that expose rows and columns as JSON documents, allowing developers to query relational data as though it were stored as JSON documents instead.

For new applications, or applications being refactored, now is the perfect time to adopt a hybrid relational/JSON data model. Identifying places where the use of JSON documents — both in the data model and in query results — can streamline development and provide greater flexibility in the future.

This is a preview of the Hybrid Relational/JSON Data Modeling and Querying Refcard. To read the entire Refcard, please download the PDF from the link above.