Last Updated on April 17, 2020

MySQL and MariaDB are relational databases. As such, they are designed to work with data that follow a precise schema.

Recently they added a JSON data type, that allows to insert semi-structured data into a column.

The webinar took place on Monday, 6th April 2020, at 15:00 London time.

Webinar Recording

Topics index:

• 3:09 – About me
• 4:16 – JSON Support
• 10:32 – Use Cases
• 21:23 – Indexing Properties
• 29:30 – Indexing Arrays
• 34:13 – Relationships
• 36:02 – Validating JSON Documents
• 40:12 – Default Properties
• 43:14 – JSON_ARRAYAGG(), JSON_OBJECTAGG()
• 47:36 – JSON_TABLE()
• 52:11 – MariaDB CONNECT

Slides

Errata and Notes

The following notes are based on feedback I had later, privately. Thank you to whoever gave me or will give me feedback. It could be used to farther expand these notes, making the webinar itself more useful.

• ERRATA: One of the slide erroneously mentions MySQL 7.0. Such version does not exist. The correct version is 5.7 – the version that preceded 8.0.
• I didn’t mention that currently adding and dropping a column can be “instantaneous”. This features have some limitations: DROP COLUMN does not free space, and ADD COLUMNM can only add a column at the end of a table (which is usually fine, but not always).
• A strategy I didn’t mention to work to work with a heterogeneous catalogue is MariaDB Dynamic Columns. This feature was never popular, probably because it is non-standard and the syntax is very verbose.
• Another strategy that I didn’t mention is Table Inheritance. This PostgreSQL feature is particularly brilliant if a taxonomy has multiple levels (types and subtypes). However it is not standard and it has some disadvantages. I don’t think it is widely used.