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.
Top 5 reasons to use MariaDB Server for JSON
- You can use transactions to insert/update multiple JSON documents
- You can query JSON documents using standard SQL functions
- You can index specific fields within JSON documents
- You can create virtual columns for specific fields within JSON documents
- You can combine relational data and JSON documents
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.
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.
SELECT NAME, JSON_VALUE(attr, "$.email.work"), JSON_VALUE(attr, "$.email.personal"), JSON_QUERY(attr, "$.interests") FROM tbl_user_profiles WHERE user_id = "sjohnson";
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