Relational + JSON:

schema flexibility with data integrity

There are times when neither normalization nor denormalization is what you want – when different rows will have different columns, but you don’t want to create a lot of tables, or a lot of columns.

You want a flexible schema.

Top 5 reasons to use relational + JSON

  1. You can use transactions to insert/modify multiple JSON documents
  2. You can apply integrity constraints to specific fields within JSON documents
  3. You can query JSON documents using standard SQL functions
  4. You can index specific fields within JSON documents
  5. You can return JSON documents as relational data, and vice-versa

Querying hybrid data models

What if you want to store user profiles in a single row? Yes, a user has user_id, name and country (and more). However, users may have personal and work email addresses as well as mobile and office phone numbers. While some users have Twitter and LinkedIn accounts, others may have Facebook and Instagram accounts. What about interests?

With JSON functions, you can store users profiles in a single row.

user_id name country attr
sjohnson Shane US
   "email": {
      "work": ""},
   "phone": {
      "mobile": "(555) 555-5555"},
   "social": {
      "twitter": "@shane_dev",
      "linkedin": "/in/shanekjohnson"},
   "interests": [
      "Science Fiction", 
      "Distributed Systems"]
SELECT name,
   JSON_VALUE(attr, "$.email.personal") AS email,
   JSON_VALUE(attr, "$.social.twitter") AS twitter,
   JSON_VALUE(attr, "$.interests[0]") AS favorite_interest
FROM   tbl_user_profiles
WHERE  user_id = "sjohnson";
name email twitter favorite_interest
Shane NULL @shane_dev Science Fiction

Recorded webinar

How to use hybrid data models (relational + JSON)

Learn how to validate, query and index JSON documents using SQL functions for JSON, introduced in MariaDB Server 10.2, and how to extend relational data models with JSON documents for increased schema flexibility.

Learn more about:

  • The pros and cons of using semi-structured data
  • The capabilities and limitations of JSON
  • How to validate JSON and maintain data integrity
  • Where and when to use JSON documents

SQL functions for JSON

MariaDB Server 10.2 introduced a comprehensive set of SQL functions for creating and querying JSON documents – everything from reading and writing fields, arrays and objects to creating JSON documents and merging relational data.

White Paper