DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

How does AI transform chaos engineering from an experiment into a critical capability? Learn how to effectively operationalize the chaos.

Data quality isn't just a technical issue: It impacts an organization's compliance, operational efficiency, and customer satisfaction.

Are you a front-end or full-stack developer frustrated by front-end distractions? Learn to move forward with tooling and clear boundaries.

Developer Experience: Demand to support engineering teams has risen, and there is a shift from traditional DevOps to workflow improvements.

  1. DZone
  2. Refcards
  3. Hybrid Relational/JSON Data Modeling and Querying
refcard 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.

Free PDF for Easy Reference
refcard cover

Written By

author avatar
,
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.

Like This Refcard? Read More From DZone

related article thumbnail

DZone Article

Using JSON in MariaDB
related article thumbnail

DZone Article

What Are the Major Advantages of Using a Graph Database?
related article thumbnail

DZone Article

Breaking to Build Better: Platform Engineering With Chaos Experiments
related article thumbnail

DZone Article

Building an IoT Framework: Essential Components for Success
related refcard thumbnail

Free DZone Refcard

Getting Started With Vector Databases
related refcard thumbnail

Free DZone Refcard

MongoDB Essentials
related refcard thumbnail

Free DZone Refcard

PostgreSQL Essentials
related refcard thumbnail

Free DZone Refcard

NoSQL Migration Essentials

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • [email protected]

Let's be friends: