Relational and Semi-Structured Data
Schema Flexibility with Data Integrity
Hybrid data modeling – using both structured and semi-structured data – can meet the flexibility requirements of modern web, mobile and IoT applications, without sacrificing ACID transactions or standard SQL.
JSON + Relational: How to use hybrid data models
Learn how to model structured and semi-structured data, index and query JSON documents with SQL and enforce the data integrity of JSON documents.
Watch nowTop 5 reasons to use relational + JSON
  You can use transactions to insert/modify multiple JSON documents.
  You can apply integrity constraints to specific fields within JSON documents.
  You can query JSON documents using standard SQL functions and JOIN JSON data with existing tables.
  You can index specific fields within JSON documents.
  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": "shane.johnson@mariadb.com"}, "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 | favorite_interest | ||
|---|---|---|---|
| Shane | none | @shane_dev | science fiction |
Flexible data modeling: JSON functions
MariaDB includes 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.
Read more
