JSON functions and dynamic columns:

flexible schemas and semi-structured data

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.

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.

White Paper: Flexible Data Modeling

Dynamic Columns

JSON and semi-structured data with MariaDB ServerRead now

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"),
  JSON_VALUE(attr, "$.email.personal"),
  JSON_QUERY(attr, "$.interests")
FROM   tbl_user_profiles
WHERE  user_id = "sjohnson";
name email_work email_home interests
Shane shane.johnson@mariadb.com NULL [Science Fiction]

Recorded Webinar

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