Introducing JSON Tables

By now you're likely aware of JavaScript Object Notation (JSON). Heck, I'd be willing to bet that there's even a good chance that you've used it for one reason or another. And, honestly, I'm sure that reason was a good one. JSON has become ubiquitous in the software industry because it provides developers with a simple and flexible way of managing data.

In the context of databases, JSON was often thought of as something you'd use with NoSQL solutions. However, over the past few years, JSON integrations have made their way into the 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 of the benefits of JSON without having to sacrifice the advantages of relational databases (e.g. SQL and all things data integrity).

MariaDB introduced built-in functions for managing JSON documents within a database in MariaDB Server 10.2. But that was only the beginning. Since then we've been working diligently and extending our JSON capabilities.

From JSON to Table

In MariaDB Server 10.6, we've added JSON_TABLE(), a powerful new function that enables you to transform JSON data directly into relational format. But, enough talk, let's take a look. To gain an understanding of the new JSON_TABLE function, let's first take a look at a simple example.

We'll start by creating a simple table, named people, that can be used to store structured data, like id and name values, as well as semi-structured data, for, say, storing a person's pets.

CREATE TABLE people (
id INT NOT NULL,
name VARCHAR(50) NOT NULL,
pets JSON
);

Next, populate the table with a new person record, including a valid JSON document containing an array of pet details.

INSERT INTO people (id, name, pets) VALUES (1, 'Rob', '[{"type":"dog","name":"Duke"},{"type":"cat","name":"Fluffy"}]');

Before MariaDB Server 10.6, if you wanted to return tabular information that has been extracted from the pets JSON field you could use previously existing JSON functions, like JSON_VALUE(), which allows you to retrieve scalar values, and JSON_QUERY(), which allows you to retrieve JSON objects, within MariaDB to get the job done.

For instance, the following query:

SELECT 
id,
JSON_VALUE(JSON_QUERY(pets, CONCAT('$[', ind.ind, ']')), '$.type') pet_type,
JSON_VALUE(JSON_QUERY(pets, CONCAT('$[', ind.ind, ']')), '$.name') pet_name
FROM
people CROSS JOIN
(SELECT 0 AS ind UNION ALL SELECT 1 AS ind) ind;

Would yield the following result:

+----------------+------------+
| id | pet_type  | pet_name   |       
+----------------+------------+
| 1  | dog       | Duke       | 
| 1  | cat       | Fluffy     |
+----------------+------------+

Yikes. While the JSON_VALUE() and JSON_QUERY() functions can be very useful in their own right, it's easy to see how using them in this context can quickly increase the complexity of a solution for what should, by all appearances, be a fairly straightforward problem to solve.

Enter the new JSON_TABLE function to make our lives much easier! By default, JSON_TABLE() returns a relational table consisting of columns specified in the COLUMNS clause, with a row for each JSON item matched by the path expression. Note that it can also be used directly within part of the FROM clause without the use of a subquery.

SELECT p.id, pt.pet_type, pt.pet_name 
FROM  
people p, 
JSON_TABLE(p.pets, 
'$[*]' COLUMNS( 
pet_type VARCHAR(10) PATH '$.type',
pet_name VARCHAR(25) PATH '$.name'
) 
)  
AS pt;

Executing the SQL statement above will yield the same result as the previous example. Yes, it's really that simple.

+----------------+------------+
| id | pet_type  | pet_name   |       
+----------------+------------+
| 1  | dog       | Duke       | 
| 1  | cat       | Fluffy     |
+----------------+------------+

Using Nested Paths

The JSON_TABLE() function also supports nested path values through the use of the NESTED PATH clause, which is used to specify nested columns.

Of course, to investigate nested path support, you'll need to have some nested data within your JSON document. Continuing with our previous example you can accomplish this by modifying the existing people record by inserting an array using the JSON_INSERT() and JSON_ARRAY() functions.

For this example, let's add a new array called favorite_foods to each of the two JSON objects within our JSON document's array.

UPDATE people 
SET  
   pets = JSON_INSERT(pets, '$[0].favorite_foods',   
                       JSON_ARRAY('chicken', 'salmon', 'carrots'));
 
UPDATE people 
SET  
   pets = JSON_INSERT(pets, '$[1].favorite_foods',   
                       JSON_ARRAY('tuna', 'turkey'));

Executing the previous SQL insert statements will modify the existing JSON document, contained within the single people record, to be the following:

[
    {
         "type": "dog",
         "name": "Duke",
         "favorite_foods": [
  "chicken", 
  "salmon", 
  "carrots"
    ]
    },
    {
         "type": "cat",
         "name": "Fluffy",
         "favorite_foods": [
  "tuna", 
  "turkey"
         ]
    }
]

In the following example, JSON path '$[*]' matches every item in the root JSON array. Then the JSON path '$.favorite_food[*]' is indicated as the NESTED PATH that matches all values in favorite_foods, which is a property name for the array you added to the existing JSON document.

SELECT p.id, pt.pet_type, pt.pet_name, pt.favorite_food 
FROM 
people p,
JSON_TABLE(p.pets, 
'$[*]' COLUMNS (
pet_type VARCHAR(10) PATH '$.type',
pet_name VARCHAR(25) PATH '$.name',
NESTED PATH '$.favorite_foods[*]' 
COLUMNS (favorite_food VARCHAR(25) PATH '$')
)
) pt;

Executing the previous SQL statement will yield the following result set:

+----------------+------------+---------------+
| id | pet_type  | pet_name   | favorite_food |        
+----------------+------------+---------------+
| 1  | dog       | Duke       | chicken       |
| 1  | dog       | Duke       | salmon        |
| 1  | dog       | Duke       | carrots       |
| 1  | cat       | Fluffy     | tuna          |
| 1  | cat       | Fluffy     | turkey        |
+----------------+------------+---------------+

Next Steps

In this blog post, you've received a very brief introduction of the new JSON_TABLE() function that is available within MariaDB Server 10.6. But this is just the tip of the iceberg. JSON_TABLE() is an extremely powerful function that allows you to transform not only simple JSON data as I've described in this blog, but also much more complex JSON documents that contain such features as arrays, nested paths, and more.