{{announcement.body}}
{{announcement.title}}
Refcard #325

Hybrid Relational/JSON Data Modeling and Querying

JSON has become the de facto standard for sending and receiving data. Within relational databases, JSON support includes hybrid data modeling and querying via standard SQL. For new applications, or those being refactored, now is the perfect time to adopt a hybrid relational/JSON data model to streamline development and provide greater flexibility in the future.

Published: Mar. 17, 2020    |    Modified: Mar. 18, 2020
1,983
Free PDF for easy Reference

Brought to you by

MariaDB
refcard cover

Written by

author avatar Shane Johnson Senior Director of Product Marketing, MariaDB
asset cover
Refcard #325

Hybrid Relational/JSON Data Modeling and Querying

JSON has become the de facto standard for sending and receiving data. Within relational databases, JSON support includes hybrid data modeling and querying via standard SQL. For new applications, or those being refactored, now is the perfect time to adopt a hybrid relational/JSON data model to streamline development and provide greater flexibility in the future.

Published: Mar. 17, 2020    |    Modified: Mar. 18, 2020
1,983
Free PDF for easy Reference

Written by

author avatar Shane Johnson Senior Director of Product Marketing, MariaDB

Brought to you by

MariaDB
Table of Contents

Introduction

Hybrid Relational/JSON Overview

Data Modeling and Querying in MariaDB Server

Quick Reference: MariaDB Server SQL JSON Functions

Conclusion

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.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}