Using JSON Functions in MariaDB and MySQL for Effective Data Manipulation

Both MariaDB and MySQL are popular relational database management systems that use structured query language (SQL) for database management. One of the most useful features of both databases is the ability to use JSON data type, which allows you to store, manipulate and query JSON documents in your database. In this blog post, we’ll take a closer look at how to use JSON functions in MariaDB and MySQL, as well as the differences between the two databases.

Want to learn joins read the Exploring MySQL Joins, Cases, and Clauses: A Beginner’s Guide

What are JSON Functions?

JSON (JavaScript Object Notation) is a lightweight data-interchange format that is easy for humans to read and write and easy for machines to parse and generate. JSON documents can contain key-value pairs, arrays, and nested objects. JSON functions in MariaDB and MySQL allow you to manipulate JSON documents and extract data from them.

JSON functions in MariaDB:

MariaDB introduced native support for JSON in version 10.2. This means that you can use JSON data type to store JSON documents in your database and perform various operations on them using JSON functions. Here are some of the most commonly used JSON functions in MariaDB:

1. JSON_EXTRACT:

This function extracts a specific value from a JSON document based on a JSON path expression. For example, if you have a JSON document that looks like this:

{
  "name": "John Doe",
  "age": 30,
  "address": {
    "street": "123 Main St",
    "city": "New York",
    "state": "NY",
    "zip": "10001"
  }
}

You can use the JSON_EXTRACT function to extract the value of the “name” key like this:

SELECT JSON_EXTRACT('{"name": "John Doe", "age": 30}', '$.name');

This will return “John Doe”.

2. JSON_SET:

This function sets a new value for a key in a JSON document. For example, if you have a JSON document that looks like this:

{
  "name": "John Doe",
  "age": 30,
  "address": {
    "street": "123 Main St",
    "city": "New York",
    "state": "NY",
    "zip": "10001"
  }
}

You can use the JSON_SET function to change the value of the “name” key like this:

SELECT JSON_SET('{"name": "John Doe", "age": 30}', '$.name', 'Jane Doe');

This will return ‘{“name”: “Jane Doe”, “age”: 30}’.

3. JSON_ARRAY:

This function creates a JSON array from a list of values. For example, if you want to create a JSON array of numbers from 1 to 5, you can use the JSON_ARRAY function like this:

SELECT JSON_ARRAY(1, 2, 3, 4, 5);

This will return ‘[1, 2, 3, 4, 5]’.

JSON functions in MySQL:

MySQL also supports JSON data type and provides a set of JSON functions for manipulating JSON documents. Here are some of the most commonly used JSON functions in MySQL:

1. JSON_EXTRACT:

This function extracts a specific value from a JSON document based on a JSON path expression. For example, if you have a JSON document that looks like this:

{
  "name": "John Doe",
  "age": 30,
  "address": {
    "street": "123 Main St",
    "city": "New York",
    "state": "NY",
    "zip": "10001"
  }
}

You can use the JSON_EXTRACT function to extract the value of the “name” key like this:

SELECT JSON_EXTRACT('{"name": "John Doe", "age": 30}', '$.name');

This will return “John Doe”.

2. JSON_SET:

This function sets a new value for a key in a JSON document. For example, if you have a JSON document that looks like this:

{
  "name": "John Doe",
  "age": 30,
  "address": {
    "street": "123 Main St",
    "city": "New York",
    "state": "NY",
    "zip": "10001"
  }
}

You can use the JSON_SET function to change the value of the “name” key like this:

SELECT JSON_SET('{"name": "John Doe", "age": 30}', '$.name', 'Jane Doe');

This will return ‘{“name”: “Jane Doe”, “age”: 30}’.

3. JSON_ARRAY:

This function creates a JSON array from a list of values. For example, if you want to create a JSON array of numbers from 1 to 5, you can use the JSON_ARRAY function like this:

SELECT JSON_ARRAY(1, 2, 3, 4, 5);

This will return ‘[1, 2, 3, 4, 5]’.

Differences between MariaDB and MySQL JSON functions:

While both MariaDB and MySQL provide similar JSON functions, there are some differences between the two databases. One major difference is the way they handle invalid JSON documents. In MariaDB, if you try to insert an invalid JSON document, the server will reject it and return an error. In MySQL, on the other hand, the server will try to correct the invalid document by adding missing quotes or braces, for example.

Another difference between the two databases is the way they handle the JSON path expression in the JSON functions. In MariaDB, you can use the standard JSON path syntax, which is based on the dot notation. In MySQL, you can use either the dot notation or the bracket notation, which is similar to the syntax used in JavaScript.

Conclusion:

JSON functions in MariaDB and MySQL provide powerful tools for manipulating and extracting data from JSON documents in your database. With the ability to use JSON data type, you can store complex data structures in your database and use JSON functions to retrieve and modify them as needed. While there are some differences between the JSON functions in MariaDB and MySQL, both databases provide similar functionality that can be useful for a wide range of applications. By mastering these functions, you can make the most of your database and improve the efficiency of your data manipulation tasks.