![]() ![]() ![]() You’ve probably picked up on it by now but managing JSON data within MariaDB really boils down to using predefined functions. INSERT INTO locations (type, name, latitude, longitude, attr) VALUES The only difference is that the string must be valid JSON. The JSON data that you insert is contained within quotes, just like any other string-based information you’d insert. The JSON_VALID() function is a predefined function that serves to receive JSON data (in the form of a string) and validate whether or not it’s valid. Most importantly, however, is the CHECK constraint, which indicates a function that will be executed when the data in attr has been modified, either through insertion or updating. Beyond that you’ll see that there are a few constraints added to the field for the character set and collation. Notice that the data type for the attr column is LONGTEXT. CREATE TABLE locations (Īttr LONGTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL SHOW CREATE TABLE locations Įxecuting the previous statement will yield the following result. Taking a closer look, we can use the SHOW CREATE query to inspect the details of what’s actually been created. That means there’s no actual JSON data type, but, instead, the JSON specified datatype assures that added data is valid JSON by adding a check constraint to the columns. More specifically the column is using a JSON alias datatype. Note that the attr column included within the locations table is defined with a JSON data type. In fact, the SQL used to create a new location table should look very familiar. Using JSON within MariaDB is as easy as enabling the ability to store JSON data within a table. So, the locations table will contain both structured and semi-structured data. But, depending on the type, each location could have different details.Įnter JSON that can be used to manage the different information per location type. We won’t be using any kind of front-end management, but imagine that the locations could be represented on some kind of map like the following.įrom the simplest standpoint, geographic locations, no matter the type, contain foundational information such as the name, type, longitude and latitude. This application will only contain one table, called locations, that will store, yep, you guessed it, locations. To help walk-through the JSON capabilities that are available within MariaDB I’m going to be using a hypothetical application. However, I’ve always found it easiest to consume new technologies by focusing on a simple, (hopefully) relatable use case that you can then use to get your own creative juices flowing. That’s just the world of software development for ya. There are a multitude of use cases where it may make sense to combine structured and semi-structured data. Tip: If you’d like to jump right into a MariaDB database and use the same dataset I’ll be using for the examples in this article be sure to check out this MariaDB JSON Quickstart Guide! Structured Data + Semi-Structured Data Let’s check out some of the JSON functionality that’s available in MariaDB and how you can use it! OK, enough of this “setting the stage” business. ![]() The ability to store JSON documents within a relational database allows you to create hybrid data models, containing both structured and semi-structured data, and enjoy all of the benefits of JSON without having to sacrifice the advantages of relational databases (e.g. Keying off of the success that NoSQL solutions have been able to achieve by using semi-structured data in that time, over the past few years JSON integrations have made their way into the relational world. And that makes sense as one of the problems the NoSQL Revolution set out to solve was flexibility, or having the ability to create, update and remove data and the structures they’re housed in without having to modify things like those pesky relational schemas. OK, but why is that important? Well, in the context of databases, JSON has often been thought of as something you’d use with NoSQL solutions. One of those capabilities is its ability to handle JavaScript Object Notation (JSON) formatted data, completely free and out-of-the-box. But, diving deeper into its capabilities, you’ll quickly discover that it’s more than that. I mean, why not? After all, MariaDB is a rock solid relational database solution, and has been for some time now. Heck, you may have even used it to create a database, a couple tables, and even executed a few queries. You’re likely familiar with MariaDB as your run-of-the-mill relational database.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |