Using JSON in MariaDB – DZone Database

I’m willing to bet that if you’re reading this article, you’ve at least heard of MariaDB. Heck, you’ve probably used it at some point. But, if not, then I Highly recommended You give it a quick look because, at the highest level, it’s very powerful and free (yes, Free) is an open source database solution that you can use for anything from a few light modifications to support for full production applications.

advice: If you want to go directly to a MariaDB database and use the same dataset in this article, be sure to check out our MariaDB JSON Quickstart guide!

Initially, at its core, MariaDB is a Relational Database Management System, or RDBMS for short, but by delving deeper into its capabilities you will quickly discover that it is more than that.

much more than that…

One such capability is its ability to handle data formatted in the JavaScript Object Notation (JSON) format, completely free of charge and beyond. OK, but why is that important? Well, in the context of databases, JSON is often seen as something you use with NoSQL solutions. This makes sense because one of the problems that the “NoSQL Revolution” was set up to solve was flexibility, or having the ability to create, update, and remove the data and structures in it without having to modify things like an annoying relational database. charts.

Structured Data vs. Semi-Structured Data

Structured Data vs. Semi-Structured Data

Building on the success that NoSQL solutions were able to achieve with semi-structured data at the time, over the past few years, JSON integrations have made their way into relational world. And for good reason. 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 the benefits of JSON without having to sacrifice the benefits of relational databases (like SQL and all things data integration).

Building on the success that NoSQL solutions were able to achieve with semi-structured data at the time, over the past few years, JSON integrations have made their way into the relationship realm.

Well, this is enough to “pave the way”. Let’s check out some of the JSON functions available in MariaDB and how you can use them. I say some because MariaDB has a great deal of JSON functionality. In fact, there is a lot to cover in just one blog post. However, we can definitely hit the high points, which should give you the foundation you need to dig deeper.

Structured Data + Semi-Structured Data

There are many use cases where it might make sense to combine structured and semi-structured data. This is just the world of software development for you. However, I’ve always found it easier to use new technologies by focusing on a simple (hopefully relevant) use case that you can then use to get your creative juices flowing.

To help learn about the capabilities of JSON that are available within MariaDB, I will be using a default implementation. This app will contain only one table called LocationsIt will store, yes, you guessed it, locations. Simple enough, right?

We wouldn’t use any kind of front-end administration, but imagine that sites can be represented on some kind of map like the following.

Use location data to put points of interest on the mapUse location data to put points of interest on the map

From the simplest point of view, geographical locations, regardless of their type, contain basic information such as name, type, longitude and latitude. But, depending on the type, each site can have different details.

Structured and semi-structured data in the same table

Structured and semi-structured data in the same table

Create table

Using JSON within MariaDB is as easy as enabling the ability to store JSON data inside a table. In fact, the SQL used to create a new location table should look very familiar.

CREATE TABLE locations (
    name VARCHAR(100) NOT NULL,  
    type CHAR(1) NOT NULL,
    latitude DECIMAL(9,6) NOT NULL,
    longitude DECIMAL(9,6) NOT NULL,
    attr JSON, 
    PRIMARY KEY (id)

We note that tar The column included in the locations table is specified with a JSON data type. More specifically, the column uses the JSON alias data type. This means that there is no actual JSON data type, but instead the specified JSON data type is converted to a data type that exists within MariaDB.

… There is no actual JSON data type, but instead the specified JSON data type is converted to a data type that exists within MariaDB.

Take a closer look, we can use SHOW CREATE Query to check details of what has already been created.


Executing the previous statement will lead to the following result.

CREATE TABLE locations (
    name VARCHAR(100) NOT NULL,  
    type CHAR(1) NOT NULL,
    latitude DECIMAL(9,6) NOT NULL,
    longitude DECIMAL(9,6) NOT NULL,
    CHECK (JSON_VALID(`attr`)

Note that the data type for file tar column LONGTEXT. Beyond that, you’ll see that there are some restrictions added to the field for character set and collation. More importantly, however, is CHECK Constraint, which indicates a function to be executed when the data is in tar It has been modified, either through insertion or update.

The JSON_VALID() function is a predefined function that receives JSON data (in the form of a string) and validates whether it is valid. Correct meaning of properly formed JSON data.

JSON data

Data Entry

The JSON data you enter is enclosed in quotes, just like any other string-based information you include. The only difference is that the string must be valid JSON.

INSERT INTO locations (type, name, latitude, longitude, attr) VALUES 
     ('R', 'Lou Malnatis', 42.0021628, -87.7255662,
  '{"details": {"foodType": "Pizza", "menu": ""}, 
     	"favorites": [{"description": "Pepperoni deep dish", "price": 18.75}, 
{"description": "The Lou", "price": 24.75}]}');

Note that you can specify different JSON data, with completely different structures, within the insert into the same table. Which makes sense, of course, because that’s the point!

INSERT INTO locations (type, name, latitude, longitude, attr) VALUES 
     ('A', 'Cloud Gate', 41.8826572, -87.6233039, 
'{"category": "Landmark", "lastVisitDate": "11/10/2019"}');

… you can specify different JSON data, with completely different structures, within the extension in the same table.

data query

You may have picked it up by now but managing JSON data inside MariaDB really boils down to using Preset mission. For the rest of this article, we’ll take a look at the many jobs available to you.

Read numerical data

The JSON_VALUE() function returns a JSON integer value from the specified path in a file Specific data. In the following example, I used a file tar as ‘defined data’, but note that the JSON provided to the function can just be a file Raw string of JSON data.

SELECT name, latitude, longitude, 
    JSON_VALUE(attr, '$.details.foodType') AS food_type
FROM locations
WHERE type="R";

Which, based on the data previously included in the sites table, can yield a result similar to the following.

MariaDB site data

And if you are wondering “what about dealing with null/non-existent values?” , because because of the nature and purpose of semi-organization, that’s kind of the point. Yes , JSON_VALUE() Post deals with that.

How about dealing with null/non-existent values? …Yes

MariaDB . data

Noticeable: This entire query and then the tabular result type flow display is something I will continue to deal with throughout this article. Just alert!

You also are not limited to using JSON functions strictly as part of SELECT item. You can just as easily use it inside the filter panes.

SELECT id, name, latitude, longitude
FROM locations
WHERE type="S" AND
    JSON_VALUE(attr, '$.details.yearOpened') = 1924;

Read object data

The JSON_QUERY() function accepts JSON data and a JSON path and returns the JSON data. the difference between JSON_VALUE() And JSON_QUERY() Is that JSON_QUERY() Returns the entire data of a JSON object.

SELECT name, description, 
    JSON_QUERY(attr, '$.details') AS details
FROM locations
WHERE type="R"

Read object data

the JSON_QUERY() The function can also return arrays.

SELECT name, description, 
    JSON_QUERY(attr, '$.teams') AS home_teams
FROM locations
WHERE type="S";

Read object data

Configure indexes

At this point, you might be wondering with all these queries running, what about the ability to create indexes (performance optimization)? Is this possible? You are really right!

It all starts with creating a virtual column.

    food_type VARCHAR(25) AS (JSON_VALUE(attr, '$.details.foodType')) VIRTUAL;

Then, you can use the virtual column, along with other virtual or permanent columns, to create a new index.

CREATE INDEX foodtypes ON locations(food_type);

Edit data

and you know, reading Data is really only half the battle. To really get the value of being able to store JSON data within a relational database, you also need to be able to modify or write it. Fortunately, MariaDB provides a bunch of functionality for this as well.

Enter fields

The JSON_INSERT() function returns JSON data generated by inserting one or more path/value pairs into the JSON data.

UPDATE locations
SET attr = JSON_INSERT(attr,'$.nickname','The Bean')
WHERE id = 8;

insert matrices

You can also create new arrays using the JSON_ARRAY() function. then inside JSON_INSERT() function, the new array can be inserted into the specified JSON data (in this case, . can be inserted tar field).

UPDATE locations
    SET attr = JSON_INSERT(attr, 
        JSON_ARRAY('Asian', 'Mexican'))
WHERE id = 1;

Adding array elements

Using the JSON_ARRAY_APPEND() function, you can modify an existing array by adding one or more elements.

UPDATE locations
    SET attr = JSON_ARRAY_APPEND(attr, 
                '$.foodTypes', 'German’)
WHERE id = 1;

remove array elements

JSON_REMOVE() can be used to remove an array element specified by the index.

UPDATE locations
    SET attr = JSON_REMOVE(attr,       
WHERE id = 1;

advice: the JSON_REMOVE() The function is so powerful that it can be used to return the output JSON document after removing any JSON data (eg an array element, object, etc) in the specified path(s) from the JSON data.

Mixed data query

It may be the case that you want to generate JSON data from structured data. So, you can use the JSON_OBJECT() function.

JSON_OBJECT('name', name, 'latitude', latitude, 'longitude', longitude) AS data
FROM locations
WHERE type="S";

Mixed data query

data merging

You can combine the data returned from a file JSON_OBJECT() function and combine it with existing JSON data using the JSON_MERGE() function. Note below that you can create a completely new JSON object with the . extension JSON_OBJECT() function, then use JSON_MERGE() Working to combine it with value tar field.

        'name', name, 
        'latitude', latitude,
        'Longitude', longitude), 
    attr) AS data
FROM locations
WHERE type="R";

JSON data merge

JSON to Spreadsheets

In MariaDB 10.6, currently, the latest version, the JSON_TABLE() function, has been added. This new function enables you to directly convert JSON data into a tabular format, which can be used directly in a file FROM An item to join other tables (or attributes).

SELECT, d.food_type,
	locations AS l,
		 ‘$’ COLUMNS(
		        food_type VARCHAR(25) PATH ‘$.foodType’,
		        menu VARCHAR(200) PATH ‘$.menu’)
           ) AS d
WHERE id = 2;

json to spreadsheets

advice: You can find a more detailed article about the new JSON_TABLE() working here!

data integrity enforcement

Finally, I’d like to touch on the ability to be able to enforce data integrity within JSON data contained within MariaDB. In more straightforward English, this means that you have the ability to create constraints, or requirements, for the types of JSON that are allowed to exist within your tables.

Here is an example of how to create a file CONSTRAINT, in this case, labeled check_attr, which specifies that for each location of type ‘S’ the JSON data within it must fit certain criteria. Specifically, you can control things like the data types of properties or values, whether a property should exist, and even the length of values ​​within a specific property. All of this is achieved using JSON functions within MariaDB. As you can see, it is very flexible and strong.

0)); “data-lang=”text/x-mariadb”>

ALTER TABLE locations ADD CONSTRAINT check_attr
        type != 'S' OR (type="S" AND 
            JSON_TYPE(JSON_QUERY(attr, '$.details')) = 'OBJECT' AND
            JSON_TYPE(JSON_QUERY(attr, '$')) = 'ARRAY' AND
            JSON_TYPE(JSON_VALUE(attr, '$.details.yearOpened')) = 'INTEGER' AND
       JSON_TYPE(JSON_VALUE(attr, '$.details.capacity')) = 'INTEGER' AND
       JSON_EXISTS(attr, '$.details.yearOpened') = 1 AND
            JSON_EXISTS(attr, '$.details.capacity') = 1 AND
            JSON_LENGTH(JSON_QUERY(attr, '$')) > 0));

Next steps

Thank you very much for reading this article on how to combine the power of MariaDB with the flexibility of JSON. But we’ve only scratched the surface of what’s available inside MariaDB.

The full list of JSON functions is currently available within MariaDB.

In the end, we all learn in different ways. If you would like to learn more about the JSON functionality that is available, and how you can get started with creating mixed data models with MariaDB, please check out the following resources I’ve compiled as well.

Learn more

If you’d like to learn more about what’s possible with MariaDB, be sure to check out the Developer Hub and our new Developer Code Central GitHub. There you can find more content just like this that includes a variety of technologies, use cases, and programming languages.

You can also dive deeper into the capabilities of MariaDB in the official documentation.

And as always, we would be nothing without our wonderful community! If you’d like to help contribute you can find us on GitHub, send us feedback directly, or join the conversation in the new MariaDB Community Slack!

Happy coding!


Leave a Comment