JSON functions and dynamic columns:

Flexible Schemas

MariaDB Server supports flexible schemas with two features: dynamic columns to create different rows with different columns and JSON functions to query, index and validate JSON documents stored in a text column.

White Paper: Flexible Data Modeling

Top 5 reasons to use MariaDB Server for JSON

  1. You can use transactions to insert/update multiple JSON documents
  2. You can query JSON documents using standard SQL functions
  3. You can index specific fields within JSON documents
  4. You can create virtual columns for specific fields within JSON documents
  5. You can combine relational data and JSON documents

Dynamic Columns

What if you want to store movies and books in a single table? While both books and movies name, year and format, movies have rating, duration and aspect ratio while books have author and page count.

With dynamic columns, you can store movies and books in the same table.

name format price attr
Tron Blu-ray 29.99
rating duration aspect_ratio
PG 96 2.21:1
Foundation Paperback 7.99
author page_count
Isaac Asimov 296

JSON Functions

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”: “shane.johnson@mariadb.com”},
                               “phone”: {
                                  “mobile”: “(555) 555-5555”},
                               “social”: {
                                  “twitter”: “@shane_dev”,
                                  “linkedin”: “/in/shanekjohnson”}
                            }
                            
                        
                
                    SELECT NAME,
                      JSON_VALUE(attr, “$.email.work”),     // to return a scalar
                      JSON_VALUE(attr, “$.email.personal”), // to return a scalar
                      JSON_QUERY(attr, “$.interests”)       // to return an array
                    FROM   tbl_user_profiles
                    WHERE  user_id = “sjohnson”;
                
            
name email_work email_home interests
Shane shane.johnson@mariadb.com NULL [Science Fiction]

Webinar Recording

JSON and semi-structured data with MariaDB Server

In this video, we explain and demonstrate, using sample data and practical examples, how to use JSON and semi-structured data in MariaDB Server.

Learn more about:

  • Pros and cons of JSON / dynamic columns
  • Capabilities and limitations of JSON functions
  • When to use JSON / dynamic columns