How the JSON_SET() function works in Mariadb?

The JSON_SET() function is a JSON function that inserts or updates data in a JSON document and returns the result as a JSON document.

Posted on

The JSON_SET() function is a JSON function that inserts or updates data in a JSON document and returns the result as a JSON document. It takes a JSON document and one or more pairs of path and value arguments. The function inserts or updates the values in the JSON document at the specified paths. If the path does not exist, the function adds it. If the path exists, the function replaces the value.

The JSON_SET() function is useful for modifying JSON documents in a flexible and dynamic way. It can also handle nested JSON objects and arrays.

Syntax

The syntax of the JSON_SET() function is as follows:

JSON_SET(json_doc, path, value[, path, value] ...)

The function takes a JSON document and one or more pairs of path and value arguments and returns a JSON document as the result. The arguments are:

  • json_doc: The JSON document to be modified.
  • path: The path to the value to be inserted or updated in the JSON document. The path must be a valid JSON path expression that begins with a dollar sign ($). The path can contain dots (.) to access object members, and brackets ([]) to access array elements. The path can also contain special characters such as *, **, and [0] to match multiple elements. For more details on the JSON path syntax, please refer to the Mariadb documentation.
  • value: The value to be inserted or updated in the JSON document. The value can be any valid JSON value, such as a string, a number, a boolean, a null, an object, or an array. The value can also be a literal value, such as a column name, a function call, or an expression.

Examples

Example 1: Inserting a new key-value pair in a JSON object

In this example, we have a JSON object that represents some information about a person. We want to insert a new key-value pair that represents the person’s gender.

SELECT JSON_SET(
  '{"name": "Alice", "age": 25, "hobbies": ["reading", "cooking"]}',
  '$.gender', 'female'
) AS result;

+-------------------------------------------------------------------------------------+
| result                                                                              |
+-------------------------------------------------------------------------------------+
| {"name": "Alice", "age": 25, "hobbies": ["reading", "cooking"], "gender": "female"} |
+-------------------------------------------------------------------------------------+

The function inserts the gender key-value pair in the JSON object and returns the result as a JSON object.

Example 2: Updating an existing value in a JSON object

In this example, we have a JSON object that represents some information about a product. We want to update the product’s price.

SELECT JSON_SET(
  '{"id": 123, "name": "Laptop", "price": 999.99}',
  '$.price', 899.99
) AS result;

+------------------------------------------------+
| result                                         |
+------------------------------------------------+
| {"id": 123, "name": "Laptop", "price": 899.99} |
+------------------------------------------------+

The function replaces the price value in the JSON object and returns the result as a JSON object.

Example 3: Inserting a new element in a JSON array

In this example, we have a JSON array that represents some reviews of a product. We want to insert a new review at the end of the array.

SELECT JSON_SET(
  '["Good quality", "Fast delivery", "Easy to use"]',
  '$[3]', 'Great value'
) AS result;

+-----------------------------------------------------------------+
| result                                                          |
+-----------------------------------------------------------------+
| ["Good quality", "Fast delivery", "Easy to use", "Great value"] |
+-----------------------------------------------------------------+

The function inserts the new element in the JSON array and returns the result as a JSON array.

Example 4: Updating an existing element in a JSON array

In this example, we have a JSON array that represents some grades of a student. We want to update the grade of the first subject.

SELECT JSON_SET(
  '[90, 85, 80]',
  '$[0]', 95
) AS result;

+--------------+
| result       |
+--------------+
| [95, 85, 80] |
+--------------+

The function replaces the first element in the JSON array and returns the result as a JSON array.

Example 5: Inserting or updating multiple values in a JSON document

In this example, we have a JSON document that represents some information about a book. We want to insert or update multiple values in the JSON document, such as the book’s rating, publisher, and ISBN.

SELECT JSON_SET(
  '{"title": "The Hitchhiker''s Guide to the Galaxy", "author": "Douglas Adams", "genre": "Science Fiction"}',
  '$.rating', 4.5,
  '$.publisher', 'Pan Books',
  '$.ISBN', '9780330258647'
) AS result;

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| result                                                                                                                                                                     |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {"title": "The Hitchhiker's Guide to the Galaxy", "author": "Douglas Adams", "genre": "Science Fiction", "rating": 4.5, "publisher": "Pan Books", "ISBN": "9780330258647"} |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

The function inserts or updates the values in the JSON document at the specified paths and returns the result as a JSON document.

There are some other JSON functions in Mariadb that are related to the JSON_SET() function. Here are some of them:

  • JSON_INSERT() function: This function inserts data into a JSON document and returns the result as a JSON document. It is similar to the JSON_SET() function, but it only inserts the values if the path does not exist. If the path exists, the function does nothing.
  • JSON_REPLACE() function: This function replaces data in a JSON document and returns the result as a JSON document. It is similar to the JSON_SET() function, but it only replaces the values if the path exists. If the path does not exist, the function does nothing.
  • JSON_REMOVE() function: This function removes data from a JSON document and returns the result as a JSON document. It takes a JSON document and one or more path arguments. The function removes the values from the JSON document at the specified paths.

For example, the following query uses the JSON_REMOVE() function to remove the rating and ISBN key-value pairs from a JSON object that represents a book:

SELECT JSON_REMOVE(
  '{"title": "The Hitchhiker''s Guide to the Galaxy", "author": "Douglas Adams", "genre": "Science Fiction", "rating": 4.5, "publisher": "Pan Books", "ISBN": "9780330258647"}',
  '$.rating', '$.ISBN'
) AS result;

+------------------------------------------------------------------------------------------------------------------------------------+
| result                                                                                                                             |
+------------------------------------------------------------------------------------------------------------------------------------+
| {"title": "The Hitchhiker's Guide to the Galaxy", "author": "Douglas Adams", "genre": "Science Fiction", "publisher": "Pan Books"} |
+------------------------------------------------------------------------------------------------------------------------------------+

The function removes the rating and ISBN key-value pairs from the JSON object and returns the result as a JSON object.

Conclusion

The JSON_SET() function is a useful JSON function that can insert or update data in a JSON document and return the result as a JSON document. It takes a JSON document and one or more pairs of path and value arguments. The function inserts or updates the values in the JSON document at the specified paths. If the path does not exist, the function adds it. If the path exists, the function replaces the value. The function can handle nested JSON objects and arrays, and can modify JSON documents in a flexible and dynamic way. There are also some other JSON functions in Mariadb that are related to the JSON_SET() function, such as JSON_INSERT(), JSON_REPLACE(), and JSON_REMOVE(). These functions can provide different ways of manipulating JSON data in Mariadb.