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.

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.

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.

*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.

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.